SQLTeam.com | Weblogs | Forums

SQL AUTHENTICATION no longer working locally, only through remote connections

We recently did an inplace upgrade from SQL2012 DEVELEOPER to SQL2014 DEVELOPER. Following the upgrade we ran a vendor's script to create a database master key, a certificate and then a symmetric key encrypted by the certificate and backup of the certificate to file location. AFTER running the code we are no longer able to connect to the instance using sql authentication from either SSMS 2012 or SSMS 2014. HOWEVER, Remote SQL Authentication connections from client computers using tcp/ip WORKS as well as OSQL/SQLCMD. We've removed the encryption mentioned above by running drop commands for the master key, certificate and symmetric key then rebooted the instance to no avail. I do not see how the above is even possible much less related to encryption designed to be used by a single user database (Database Master Key). Has ANYONE seen anything like this before? Quite a few very bright people are stumped by this one so I'm reaching out to the community for assistance.
Nix_Headshot

Here is the Generic Code with names/pw redacted of course.

BEGIN TRY
BEGIN TRANSACTION

--Create master key (database level) using password 
IF NOT EXISTS (SELECT 1 FROM sys.symmetric_keys where name like 
    '%DatabaseMasterKey%' )
	CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'SecurePassword'
ELSE
	OPEN MASTER KEY DECRYPTION BY PASSWORD = 'SecurePassword'
	ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
    CLOSE MASTER KEY

--Create Encryption Self Signed Cert
CREATE CERTIFICATE SDSignedCertificate WITH SUBJECT = 'PII/SPII'
--CREATE SYMETRIC KEY
CREATE SYMMETRIC KEY [SymetricKeyName]
	WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE SDSignedCertificate
--Backup the certificate to the specified backup file path
BACKUP CERTIFICATE SDSignedCertificate
	TO FILE = 'BackupFilePath\SDSignedCertificate.cer'
	WITH PRIVATE KEY (FILE = 'BackupFilePath\SDSignedCertificate.pvk',
	ENCRYPTION BY PASSWORD = 'SecurePassword')

COMMIT TRANSACTION
END TRY
-- Error handling and Rollback transaction
BEGIN CATCH

DECLARE @Message varchar(MAX)=ERROR_MESSAGE(),@Severity int =ERROR_SEVERITY(), @State smallint=ERROR_STATE()
RAISERROR(@Message,@Severity,@State)
ROLLBACK TRANSACTION

END CATCH