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.

A transport-level error has occurred when receiving results from the server

-> Application logs shows below error everyday intermittently between 12:00 AM to 12:10 AM,

Timestamp: 5/17/2020 10:05:58 PM
Message: HandlingInstanceID: c190c221-3e43-4702-83fa-533122cb3c81
An exception of type ‘System.Data.SqlClient.SqlException’ occurred and was caught.
———————————————————————————-
05/18/2020 00:05:58
Type : System.Data.SqlClient.SqlException, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
Message : A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 – The semaphore timeout period has expired.)
Source : .Net SqlClient Data Provider
Help link :
Errors : System.Data.SqlClient.SqlErrorCollection
ClientConnectionId : c43b433e-4649-48f5-b0bf-05dca3d83432
Class : 20
LineNumber : 0
Number : 121
Procedure :
Server : JBDB01
State : 0
ErrorCode : -2146232060
Data : System.Collections.ListDictionaryInternal
TargetSite : Void OnError(System.Data.SqlClient.SqlException, Boolean, System.Action`1[System.Action])
HResult : -2146232060
Stack Trace : at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
at System.Data.SqlClient.TdsParserStateObject.ReadSniSyncOverAsync()
at System.Data.SqlClient.TdsParserStateObject.TryReadNetworkPacket()
at System.Data.SqlClient.TdsParserStateObject.TryPrepareBuffer()
at System.Data.SqlClient.TdsParserStateObject.TryReadByte(Byte& value)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at Microsoft.Practices.EnterpriseLibrary.Data.Database.DoLoadDataSet(IDbCommand command, DataSet dataSet, String[] tableNames)
at Microsoft.Practices.EnterpriseLibrary.Data.Database.LoadDataSet(DbCommand command, DataSet dataSet, String[] tableNames)
at Data.Export.GetNextCreatedExport()
at Business.Export.GetNextCreatedExport()
at Facade.Export.GetNextCreatedExport()
Additional Info:
MachineName : JBAPP01
TimeStamp : 5/17/2020 10:05:58 PM
FullName : Microsoft.Practices.EnterpriseLibrary.ExceptionHandling, Version=6.0.0.0, Culture=neutral, PublicKeyToken=daf167d5325e0d31
ThreadIdentity : System User
Inner Exception
—————
Type : System.ComponentModel.Win32Exception, System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
Message : The semaphore timeout period has expired
Source :
Help link :
NativeErrorCode : 121
ErrorCode : -2147467259
Data : System.Collections.ListDictionaryInternal
TargetSite :
HResult : -2147467259
Stack Trace : The stack trace is unavailable.
Category: Error
Priority: 2
EventId: 100
Severity: Error
Title:Enterprise Library Exception Handling
Machine: JBAPP01
Process Id: 6868
Win32 Thread Id: 11312
Thread Name:
Extended Properties: HelpLink.ProdName – Microsoft SQL Server
HelpLink.EvtSrc – MSSQLServer
HelpLink.EvtID – 121
HelpLink.BaseHelpUrl – http://go.microsoft.com/fwlink
HelpLink.LinkId – 20476

-> The error message at first glance seems like a network issue between Application and database server OR unresponsive database server due to high resource utilization.

-> I setup a perfmon collection on the database server and also started a continuous ping on Application server JBAPP01 pinging Database Server JBDB01 continuously using below powershell command,

ping.exe -t JBDB01|Foreach{"{0} - {1}" -f (Get-Date),$_} > C:\temp\ping\JBDB01.txt

-> The same issue happened. I checked the perfmon counters on the database server. Resource utilization was normal.

-> SQL Server error log and Event Viewer logs on database server did not have any errors during the time the error happened on the application server.

-> Checked the output of the ping command on Application Server and got below details,

5/18/2020 12:05:45 AM – Reply from 192.36.67.12: bytes=32 time<1ms TTL=127
5/18/2020 12:05:46 AM – Reply from 192.36.67.12: bytes=32 time<1ms TTL=127
5/18/2020 12:05:51 AM – Request timed out.
5/18/2020 12:05:56 AM – Request timed out.
5/18/2020 12:06:01 AM – Request timed out.
5/18/2020 12:06:06 AM – Request timed out.
5/18/2020 12:06:11 AM – Request timed out.
5/18/2020 12:06:12 AM – Reply from 192.36.67.12: bytes=32 time<1ms TTL=127
5/18/2020 12:06:13 AM – Reply from 192.36.67.12: bytes=32 time<1ms TTL=127

-> From the above output it is clear that the application is experiencing connectivity errors when there is packet loss as indicated by “Request Timed Out” from above output.

-> This was taken up with Network team. They worked with Windows team and advised that the packet loss are as a result of daily Server snapshot that was performed at that time.

-> Application team updated us that the functionality that is failing every day between 12:00 AM to 12:10 AM is some sort of health monitor that checks if all components of the Application including Database server is healthy. Since the error doesn’t have any business impact, they changed the schedule to skip between 12:00 AM to 12:15 AM and this resolved 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.