Nirav's space

Quote

SQL Server 2005/2008 SSL Encryption issue with the Windows Server 2008/R2 and Windows 7

Dear All,

Hope you are very well and enjoying your journey with the SQL Server.

Issue Description:

Last week i came across very strange issue with the SQL Server on Windows Server 2008/R2 or Client Machine on Windows 7(32-bit /64-Bit)

Let me give you some brief about this issue so we have better understanding of what we are doing.

I was trying to load SSL Certificate in to the one of the SQL Server 2005 Cluster Instance on Three node active passive windows Server 2008 R2 64 -bit cluster environment.

In our Infrastructure i do have certificate server as well which was running on Windows Server 2003 R2+SP2(64-Bit) Enterprise Edition.

I was also following one well known blog for the Enable SSL Encryption on the SQL Server cluster

http://blogs.msdn.com/jorgepc/archive/2008/02/19/enabling-certificates-for-ssl-connection-on-sql-server-2005-clustered-installation.aspx

I have gone through the above two articles and then i was able to understand whole concept behind the encryption and what is the important of Encryption in the SQL Server.

1). Now in Every one mind first question comes that what is the important of the SSL Certificate and Self SSL Certificate in the SQL Server Environment.

I would say here "Excellent  Question"

Now let me break down this question in to two parts so that you can able to understand clearly

A). SQL Server by default load one certificate during the startup of the SQL Server instance and as we all know this Certificate known as Self SSL Certificate which is exclusively generated by the SQL Server and used by the SQL Server.

If you open the error log of the SQL Server you will always this error message in to the SQL Server error log

2010-03-16 13:19:06.91 Server      A self-generated certificate was successfully loaded for encryption.
2010-03-16 13:19:06.91 Server

Now according to the above message we think that SQL Server has created self certificate and loaded it for encryption even though we don’t want any encryption to be enable on the SQL Server still why on earth SQL Server loading Self-Generated certificate.

Answer is very simple when you using your sql Server for any purpose does not matter whether you have enable force Encryption or not as security measurement SQL Server  has to encrypt the login detail pass through the network wire so if you are using the SQL Authentication to connect to your SQL Server. When you making connection to your SQL Server from any client that data will go across the N/W wire if someone has Network Tracer Tool then they can easily what data you have sent with which user credential and which user password.

–I will show you example of it here.Where SQL Server loaded Self Generated Certificate for the Encryption.

–Now when we made connection from the SQL Server Client to SQL Server server using the SQL Server authentication.( In my case)

— Very first step SQL Server client will send the data like server name and user name and password to server in order to authenticate the current user.

— Now this data (UserName and Password) will travel across N/W wire and without encryption consider when someone put Network Tracer in between the Server and client N/W Wire and if he can able to get this data then he very easily identify what is the username and password for this server pass by the client and they can hack the password and username.

— Interesting thing here in the SQL Server above scenario will only possible if the important data like username and password sent across to N/W Wire to the server without encryption.

— Now we will able to understand what is major role of the our "HERO" so called Self Generated SSL Certificate.

— The role of the Self Generated SSL Certificate is to Encrypt the login name and password sent from the  Client Machine to the server.So that if someone try to trace that N/W Packet they won’t be able to understand the what is the UserName and Password

— Please find the following detail how the UserName and Password Look like From Network Tracer Tool.

— As you can see in the above figure where i have highlighted one portion of Network Monitor Trace with red pen and another inner portion with blue pen.

— Highlighted with the Blue Pen is the actual encryption of the username and password when transmitting across network wire.

— Now here we understand that Self Generated SSL Certificate is used for the Encrypting username and password but when we execute the SQL Server query which will also go through the N/W Wire as well so will that SQL Server Query is also encrypted.

— Let’ us see in the below figure where i have connected with the one of the SQL Server instance and this instance has only Self Generated Certificate for the Encryption.

— We first Create Table in the SQL Server Instance and then insert two rows in that table

— Now from the client application we have started one connection and we have also started the Netmon Network Tracer Tool in between Client and Server and then we got the following Results from the Network Tracer.

— Now if you see in the below images where i have highlighted portion with the red pen that portion is SQL Server query which we cane easily able to make out. what client has sent query to server.

— Now another question when Server will reply to this client as result of the above query it will also not encrypt the data and that data look like this in the below images.

— Now this data is just simple data but it could be possible if you have very sensitive data like salary and bank account detail so it would always advisable that you must encrypt this data from the client to server and from the server to client.

 

Above Explanation of the SSL Certificate how important SSL Certificate when you want secure environment for your Sensitive data.

–Now as i have identified that i must use the SSL Certificate to protect my sensitive data over the N/W wire.

— I decided to load the SSL certificate from the Certificate server in to my production SQL Server 2005 clustered Instance.

— So  I logged on to my active node which was (Node1) and my SQL Server instance was running (active) on that node.

— I did request the certificate from one of the SQL Server nodes. According to the Books Online article, the certificate should be installed using the SQL Service account and it has to be placed on the local computer > personal certificates store:

Certificates are stored locally for the users on the computer. To install a certificate for use by SQL Server, you must be running SQL Server Configuration Manager under the same user account as the SQL Server service unless the service is running as LocalSystem, NetworkService, or LocalService, in which case you may use an administrative account.

This is because the certificate contains a private key that needs to be accessed by SQL Server service account for the SSL communication to work. If we use any other user account (say, local administrator) SQL Server service account will not be able to access the private key and the authentication will fail. The certificate can be optionally installed on the current user certificate store, but installing it on the computer certificate store makes the certificate available for any user.

I did logon in the server using the SQL Service account, since this is a cluster, this has to be a domain account. I did open the web enrollment site (by default under http://CAName/certsvr, where CAName is the Certification Authority’s hostname) and requested a new Server Authentication Certificate. The most important thing at this point is to specify the fully qualify DNS name of the clustered instance in the "Name" property for this certificate; the SSL encryption will not work if we specify the name of the cluster node:

If you want to use encryption with a failover cluster, you must install the server certificate with the fully qualified DNS name of the failover clustered instance on all nodes in the failover cluster. For example, if you have a two-node cluster, with nodes named test1.your company.com and test2. your company.com and a failover clustered instance of SQL Server named fcisql, you must obtain a certificate for fcisql.your company.com and install the certificate on both nodes.

–In my case, the SQL Network Name cluster resource was called "SQLVIM" so I did specify SQLVIM.hpfareast.com as name property of the Certificate.

–While requesting the certificate, make sure that the option "Mark keys as exportable" is enabled so the private key is available when exporting and importing the certificate in the rest of the cluster nodes.

–I did also check the "Store certificate in the local computer certificate store" so I do not have to import the certificate later. After submitting the certificate request I did back to my CA and approved the request. Finally I returned to the web enrollment site to install the certificate.

–To configure SSL encryption to work with a certificate from a public certification authority follow the below steps:

1. Generate a certificate with the following requirements
   • Certificate CSP should be “Microsoft RSA SChannel Cryptographic Provider”
   • The certificate must be meant for server authentication. This requires the Enhanced Key Usage property of the certificate to specify Server Authentication (1.3.6.1.5.5.7.3.1)
   • The Subject property of the certificate must indicate that the common name (CN) is the same as the host name or fully qualified domain name (FQDN) of the server computer. If SQL Server is running on a failover cluster, the common name must match the host name or FQDN of the virtual server and the certificates must be provisioned on all nodes in the failover cluster
2. Import the same certificate on both nodes into the following locations
   • Computer container
   • SQL services account personal container
3. Add the SQL service account into the local administrator group of both cluster nodes

— Then i verified that i have my SQL Server cluster certificate installed in to the right location using the MMC console and then add snap in of Certificate with the local computer.

— Now here is point all this things has done at your end then you will have to just tweak one of registry key for the SQL Server instance. which is as following.

— You have to open again MMC console of the SQL Server and then add snap in of Certificate with the local computer.Now double click on the certificate.Now go to detail tab of the certificate and click on the Thumbprint of the certificate which look like as following

 

— Now we just need to copy this thumbprint and paste it in to notepad and then remove all the leading space

— Now just copy that character from the notepad and paste it in the following Certificate registry key of the SQL Server

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLServer\SuperSocketNetLib\Certificate

— One more registry you have to change that is “ForceEncryption” to 1 that means no matter whether SQL Server client want to do encryption or not by default every connection will encrypted.(Please carry this same procedure in the all the available nodes)

— Now after changing this key we have recycle the SQL server in order make this certificate working. So  i went ahead and try to bring offline the SQL server and then bring it online and it failed with the very generic error message which is as following

 

2010-03-17 11:32:14.74 spid9s      Starting up database ‘model’.

2010-03-17 11:32:14.74 spid5s      The NETBIOS name of the local node that is running the server is ‘SQLVIM’. This is an informational message only. No user action is required.

2010-03-17 11:32:14.73 Server      Error: 17182, Severity: 16, State: 1.

2010-03-17 11:32:14.73 Server      TDSSNIClient initialization failed with error 0xd, status code 0x38.

2010-03-17 11:32:14.77 Server      Error: 17182, Severity: 16, State: 1.

2010-03-17 11:32:14.77 Server      TDSSNIClient initialization failed with error 0xd, status code 0x1.

2010-03-17 11:32:14.77 Server      Error: 17826, Severity: 18, State: 3.

2010-03-17 11:32:14.77 Server      Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.

2010-03-17 11:32:14.77 Server      Error: 17120, Severity: 16, State: 1.

2010-03-17 11:32:14.77 Server      SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

— We tried a lot but nothing was able to resolve this issue then final MS engineer has found following solution which declared as “BUG” from windows Team

—  In Windows 2008 R2, the certificates applet adds some kind of space character in front of the thumbprint a very nasty behavior in the certificate applet of Windows 2008R2!

 

–Compare this with the same dialogue on Windows 2003 SP2:

– Now the evil thing about this leading "space character is, that it is NOT a normal space character!

  Once you copy it to a Unicode text editor, it gets invisible! But when you mark a complete line of text, it will get copied as well!

– Only when you force to save the Unicode text as ANSI file from a text editor (e.g. Notepad), you’ll see this character gets converted to "?"!

Note: Your SVR-SQL-CLU02_SQL_CLU-SQL2005-T1_SQL2005T1_REG.txt, you sent us shows these registry values:

HKEY_LOCAL_MACHINE\software\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\SuperSocketNetLib
    ForceEncryption    REG_DWORD    0x1
    HideInstance    REG_DWORD    0x0
    Certificate    REG_SZ    ?95036285e17dd6b36b7a38d94396ba052c327786
    DisplayName    REG_SZ    SQL Server Network Configuration

To confirm, if you have currently this situation or not, please follow these steps:

1) Open regedit

2) Navigate in the left pane to the registry key where you need to configure the certificate

    (e.g. HKEY_LOCAL_MACHINE\software\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\SuperSocketNetLib)

3) Rightclick on this key and select "Export"

4) Save this registry key and all values below as a *.reg file

5) Open the *.reg file from step 4 in Notepad

    Note: As a *.reg file is stored in Unicode, the value looks fine!

    E.g. "Certificate"="90223066659e6f92af3b4d87962b0502ddc97786"

6) Now choose menu File- Save As and save the *.reg file to a different file name choosing Encoding=ANSI!

7) Close Notepad

8) Open Notepad again and load the ANSI file from step 6)

Result:

a) If you now see a question mark or other "strange characters" in the certificate value, you have hit this issue!

    E.g. "Certificate"="?90223066659e6f92af3b4d87962b0502ddc97786"

– To avoid this kind of issue in the future, ensure that you don’t mark the very first "space character", when you copy the thumbprint from the certificates MMC!

Dear All,

Hope you are very well and enjoying your journey with the SQL Server.

Issue Description:

Last week i came across very strange issue with the SQL Server on Windows Server 2008/R2 or Client Machine on Windows 7(32-bit /64-Bit)

Let me give you some brief about this issue so we have better understanding of what we are doing.

I was trying to load SSL Certificate in to the one of the SQL Server 2005 Cluster Instance on Three node active passive windows Server 2008 R2 64 -bit cluster environment.

In our Infrastructure i do have certificate server as well which was running on Windows Server 2003 R2+SP2(64-Bit) Enterprise Edition.

I was also following one well known blog for the Enable SSL Encryption on the SQL Server cluster

http://blogs.msdn.com/jorgepc/archive/2008/02/19/enabling-certificates-for-ssl-connection-on-sql-server-2005-clustered-installation.aspx

I have gone through the above two articles and then i was able to understand whole concept behind the encryption and what is the important of Encryption in the SQL Server.

1). Now in Every one mind first question comes that what is the important of the SSL Certificate and Self SSL Certificate in the SQL Server Environment.

I would say here "Excellent  Question"

Now let me break down this question in to two parts so that you can able to understand clearly

A). SQL Server by default load one certificate during the startup of the SQL Server instance and as we all know this Certificate known as Self SSL Certificate which is exclusively generated by the SQL Server and used by the SQL Server.

If you open the error log of the SQL Server you will always this error message in to the SQL Server error log

2010-03-16 13:19:06.91 Server      A self-generated certificate was successfully loaded for encryption.
2010-03-16 13:19:06.91 Server

Now according to the above message we think that SQL Server has created self certificate and loaded it for encryption even though we don’t want any encryption to be enable on the SQL Server still why on earth SQL Server loading Self-Generated certificate.

Answer is very simple when you using your sql Server for any purpose does not matter whether you have enable force Encryption or not as security measurement SQL Server  has to encrypt the login detail pass through the network wire so if you are using the SQL Authentication to connect to your SQL Server. When you making connection to your SQL Server from any client that data will go across the N/W wire if someone has Network Tracer Tool then they can easily what data you have sent with which user credential and which user password.

–I will show you example of it here.Where SQL Server loaded Self Generated Certificate for the Encryption.

–Now when we made connection from the SQL Server Client to SQL Server server using the SQL Server authentication.( In my case)

— Very first step SQL Server client will send the data like server name and user name and password to server in order to authenticate the current user.

— Now this data (UserName and Password) will travel across N/W wire and without encryption consider when someone put Network Tracer in between the Server and client N/W Wire and if he can able to get this data then he very easily identify what is the username and password for this server pass by the client and they can hack the password and username.

— Interesting thing here in the SQL Server above scenario will only possible if the important data like username and password sent across to N/W Wire to the server without encryption.

— Now we will able to understand what is major role of the our "HERO" so called Self Generated SSL Certificate.

— The role of the Self Generated SSL Certificate is to Encrypt the login name and password sent from the  Client Machine to the server.So that if someone try to trace that N/W Packet they won’t be able to understand the what is the UserName and Password

— Please find the following detail how the UserName and Password Look like From Network Tracer Tool.

— As you can see in the above figure where i have highlighted one portion of Network Monitor Trace with red pen and another inner portion with blue pen.

— Highlighted with the Blue Pen is the actual encryption of the username and password when transmitting across network wire.

— Now here we understand that Self Generated SSL Certificate is used for the Encrypting username and password but when we execute the SQL Server query which will also go through the N/W Wire as well so will that SQL Server Query is also encrypted.

— Let’ us see in the below figure where i have connected with the one of the SQL Server instance and this instance has only Self Generated Certificate for the Encryption.

— We first Create Table in the SQL Server Instance and then insert two rows in that table

— Now from the client application we have started one connection and we have also started the Netmon Network Tracer Tool in between Client and Server and then we got the following Results from the Network Tracer.

— Now if you see in the below images where i have highlighted portion with the red pen that portion is SQL Server query which we cane easily able to make out. what client has sent query to server.

— Now another question when Server will reply to this client as result of the above query it will also not encrypt the data and that data look like this in the below images.

— Now this data is just simple data but it could be possible if you have very sensitive data like salary and bank account detail so it would always advisable that you must encrypt this data from the client to server and from the server to client.

Above Explanation of the SSL Certificate how important SSL Certificate when you want secure environment for your Sensitive data.

–Now as i have identified that i must use the SSL Certificate to protect my sensitive data over the N/W wire.

— I decided to load the SSL certificate from the Certificate server in to my production SQL Server 2005 clustered Instance.

— So  I logged on to my active node which was (Node1) and my SQL Server instance was running (active) on that node.

— I did request the certificate from one of the SQL Server nodes. According to the Books Online article, the certificate should be installed using the SQL Service account and it has to be placed on the local computer > personal certificates store:

Certificates are stored locally for the users on the computer. To install a certificate for use by SQL Server, you must be running SQL Server Configuration Manager under the same user account as the SQL Server service unless the service is running as LocalSystem, NetworkService, or LocalService, in which case you may use an administrative account.

This is because the certificate contains a private key that needs to be accessed by SQL Server service account for the SSL communication to work. If we use any other user account (say, local administrator) SQL Server service account will not be able to access the private key and the authentication will fail. The certificate can be optionally installed on the current user certificate store, but installing it on the computer certificate store makes the certificate available for any user.

I did logon in the server using the SQL Service account, since this is a cluster, this has to be a domain account. I did open the web enrollment site (by default under http://CAName/certsvr, where CAName is the Certification Authority’s hostname) and requested a new Server Authentication Certificate. The most important thing at this point is to specify the fully qualify DNS name of the clustered instance in the "Name" property for this certificate; the SSL encryption will not work if we specify the name of the cluster node:

If you want to use encryption with a failover cluster, you must install the server certificate with the fully qualified DNS name of the failover clustered instance on all nodes in the failover cluster. For example, if you have a two-node cluster, with nodes named test1.your company.com and test2. your company.com and a failover clustered instance of SQL Server named fcisql, you must obtain a certificate for fcisql.your company.com and install the certificate on both nodes.

–In my case, the SQL Network Name cluster resource was called "SQLVIM" so I did specify SQLVIM.hpfareast.com as name property of the Certificate.

–While requesting the certificate, make sure that the option "Mark keys as exportable" is enabled so the private key is available when exporting and importing the certificate in the rest of the cluster nodes.

–I did also check the "Store certificate in the local computer certificate store" so I do not have to import the certificate later. After submitting the certificate request I did back to my CA and approved the request. Finally I returned to the web enrollment site to install the certificate.

–To configure SSL encryption to work with a certificate from a public certification authority follow the below steps:

1. Generate a certificate with the following requirements
   • Certificate CSP should be “Microsoft RSA SChannel Cryptographic Provider”
   • The certificate must be meant for server authentication. This requires the Enhanced Key Usage property of the certificate to specify Server Authentication (1.3.6.1.5.5.7.3.1)
   • The Subject property of the certificate must indicate that the common name (CN) is the same as the host name or fully qualified domain name (FQDN) of the server computer. If SQL Server is running on a failover cluster, the common name must match the host name or FQDN of the virtual server and the certificates must be provisioned on all nodes in the failover cluster
2. Import the same certificate on both nodes into the following locations
   • Computer container
   • SQL services account personal container
3. Add the SQL service account into the local administrator group of both cluster nodes

— Then i verified that i have my SQL Server cluster certificate installed in to the right location using the MMC console and then add snap in of Certificate with the local computer.

— Now here is point all this things has done at your end then you will have to just tweak one of registry key for the SQL Server instance. which is as following.

— You have to open again MMC console of the SQL Server and then add snap in of Certificate with the local computer.Now double click on the certificate.Now go to detail tab of the certificate and click on the Thumbprint of the certificate which look like as following

— Now we just need to copy this thumbprint and paste it in to notepad and then remove all the leading space

— Now just copy that character from the notepad and paste it in the following Certificate registry key of the SQL Server

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLServer\SuperSocketNetLib\Certificate

— One more registry you have to change that is “ForceEncryption” to 1 that means no matter whether SQL Server client want to do encryption or not by default every connection will encrypted.(Please carry this same procedure in the all the available nodes)

— Now after changing this key we have recycle the SQL server in order make this certificate working. So  i went ahead and try to bring offline the SQL server and then bring it online and it failed with the very generic error message which is as following

2010-03-17 11:32:14.74 spid9s      Starting up database ‘model’.

2010-03-17 11:32:14.74 spid5s      The NETBIOS name of the local node that is running the server is ‘SQLVIM’. This is an informational message only. No user action is required.

2010-03-17 11:32:14.73 Server      Error: 17182, Severity: 16, State: 1.

2010-03-17 11:32:14.73 Server      TDSSNIClient initialization failed with error 0xd, status code 0x38.

2010-03-17 11:32:14.77 Server      Error: 17182, Severity: 16, State: 1.

2010-03-17 11:32:14.77 Server      TDSSNIClient initialization failed with error 0xd, status code 0x1.

2010-03-17 11:32:14.77 Server      Error: 17826, Severity: 18, State: 3.

2010-03-17 11:32:14.77 Server      Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.

2010-03-17 11:32:14.77 Server      Error: 17120, Severity: 16, State: 1.

2010-03-17 11:32:14.77 Server      SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

— We tried a lot but nothing was able to resolve this issue then final MS engineer has found following solution which declared as “BUG” from windows Team

—  In Windows 2008 R2, the certificates applet adds some kind of space character in front of the thumbprint a very nasty behavior in the certificate applet of Windows 2008R2!

–Compare this with the same dialogue on Windows 2003 SP2:

– Now the evil thing about this leading "space character is, that it is NOT a normal space character!

  Once you copy it to a Unicode text editor, it gets invisible! But when you mark a complete line of text, it will get copied as well!

– Only when you force to save the Unicode text as ANSI file from a text editor (e.g. Notepad), you’ll see this character gets converted to "?"!

Note: Your SVR-SQL-CLU02_SQL_CLU-SQL2005-T1_SQL2005T1_REG.txt, you sent us shows these registry values:

HKEY_LOCAL_MACHINE\software\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\SuperSocketNetLib
    ForceEncryption    REG_DWORD    0x1
    HideInstance    REG_DWORD    0x0
    Certificate    REG_SZ    ?95036285e17dd6b36b7a38d94396ba052c327786
    DisplayName    REG_SZ    SQL Server Network Configuration

To confirm, if you have currently this situation or not, please follow these steps:

1) Open regedit

2) Navigate in the left pane to the registry key where you need to configure the certificate

    (e.g. HKEY_LOCAL_MACHINE\software\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\SuperSocketNetLib)

3) Rightclick on this key and select "Export"

4) Save this registry key and all values below as a *.reg file

5) Open the *.reg file from step 4 in Notepad

    Note: As a *.reg file is stored in Unicode, the value looks fine!

    E.g. "Certificate"="90223066659e6f92af3b4d87962b0502ddc97786"

6) Now choose menu File- Save As and save the *.reg file to a different file name choosing Encoding=ANSI!

7) Close Notepad

8) Open Notepad again and load the ANSI file from step 6)

Result:

a) If you now see a question mark or other "strange characters" in the certificate value, you have hit this issue!

    E.g. "Certificate"="?90223066659e6f92af3b4d87962b0502ddc97786"

– To avoid this kind of issue in the future, ensure that you don’t mark the very first "space character", when you copy the thumbprint from the certificates MMC!

Quote

Replication : Configuring Web Synchronization on Windows Vista for Merge Replication, SQL Compact Su

Quote

How to: Configure Web Synchronization for Merge Replication (SQL Server Management Studio)

Quote

Service Architecture (Reporting Services)