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.

Log Shipping – Copy and Restore job failing with no error message

-> I was setting up log shipping to ship transaction logs from our primary server to Disaster Recovery server.

-> Log Shipping setup completed fine without any errors. But the SQL Server agent jobs for Copy and restore job were failing with no meaning full message. Below message was found in SQL Server agent history,

Executed as JBS\jvivek2k1

-> I opened the copy job and added an ouput file to the step and started the job again. The job failed and the output file was blank.

-> I opened the copy job again and copied the content of the copy job which basically calls sqllogship.exe and executed in a command prompt and got below message,

The following feature couldn’t be installed : .NET framework 3.5 (includes .NET 2.0 and 3.0)

Windows Server roles and features cannot be automatically installed or uninstalled via the Windows Features Control Panel.

To Install Windows Server roles and features, start Server Manager, or use the Server Manager cmdlets for Windows PowerShell.

-> I installed .NET 3.5 manually and rebooted the server. This solved the issue.

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.