SQL Server Configuration Manager missing

-> SQL Server configuration manager was missing on one of the Database Server,

-> I searched for “SQLServerManager*” on my database server and found below,

-> I can see “SQLServerManager15.msc” in location C:\Windows\System32.

Option 1

Double click on SQLServerManager15.msc. SQL server configuration manager will be opened and can be used.

Option 2

-> Right click some where in desktop. Click New -> “Shortcut”,

-> Double click on the shortcut “SQLServerManager15.msc” created. You will be able to use SQLS erver configuration manager.

Option 3

-> Click Start -> Run -> mmc.exe.

-> Click File -> “Add/Remove Snap-in…”

-> Select “SQL Server Configuration Manager” and click Add,

-> Click “OK”. We will be able to use SQL Server configuration manager,

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Reason: Server is in single user mode. Only one administrator can connect at this time.

-> I had to start SQL server in single user mode. I opened services.msc and then right clicked SQL Services and noted down the “Service name”,

-> I then started SQL Services in single user mode using below command from command prompt,

 net start MSSQL$IN2019 /m

-> It started fine, I tried connecting to the SQL server using SQLCMD and got below error,

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login failed for user ”. Reason: Server is in single user mode. Only one administrator can connect at this time..

-> I tried connecting to SQL Server using SQL Server management Studio and got below same error,

TITLE: Connect to Server
Cannot connect to JBResearch\IN2019.

ADDITIONAL INFORMATION:
Login failed for user ”. Reason: Server is in single user mode. Only one administrator can connect at this time. (Microsoft SQL Server, Error: 18461)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=18461&LinkId=20476
BUTTONS:
OK

-> I stopped SQL services and also stopped all other SQL related services,

-> I then used below query to start the SQL server in single user mode that allows only SQLCMD to make connection,

Net Start MSSQL$IN2019 /m”SQLCMD”

-> It worked fine this time.

-> If you want to use SQL Server Management studio instead of SQLCMD, then you can use below command,

net start MSSQL$IN2019 -m"Microsoft SQL Server Management Studio - Query"

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Restore database backup that contains column encrypted objects

-> I performed a database restore with a backup on a development server as advised by my application team.

-> The database restore went fine. Application team performed testing and reported below error when trying to decrypt an encrypted column.

Msg 15581, Level 16, State 7, Line 2
Please create a master key in the database or open the master key in the session before performing this operation.

-> To solve this problem, I executed below query on SQL Server Instance where the database was restored,

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Passw0rd' -- Password should be same as what was used with query "CREATE MASTER KEY ENCRYPTION BY PASSWORD"
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
CLOSE MASTER KEY

-> Execute below query to fetch the decrypted data,

OPEN SYMMETRIC KEY Table1_Sym_Key DECRYPTION BY CERTIFICATE Table1_Certificate;
SELECT Customer_ID,Customer_PWD AS 'Encrypted data',
CONVERT(nvarchar, DecryptByKey(Customer_PWD)) AS 'Decrypted Customer Password'
 FROM Table1;
CLOSE SYMMETRIC KEY Table1_Sym_Key;
GO

-> It worked fine. Below action should be taken if no manula steps is required.

-> Before restoring a database backup that contains column encrypted objects on a different SQL Server instance. Below actions should be performed.

-> Execute below query to backup the Service Master Key where the database backup was performed.

BACKUP SERVICE MASTER KEY
 TO FILE = 'C:\temp\JBSAG1_Service_master_key'
ENCRYPTION BY PASSWORD = 'Passw0rd';

-> Copy the backup of Service Master Key to SQL Server Instance where restore should be performed.

-> Execute below query on destination SQL Server Instance where restore will be performed,

RESTORE SERVICE MASTER KEY
FROM FILE = 'C:\temp\JBSAG1_Service_master_key'
DECRYPTION BY PASSWORD = 'Passw0rd';
GO

-> Once above tasks are completed. Any further restores will not have any issues querying the encrypted data.

-> Check this article if you are encountering this issue on an Always ON availability group environment.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.