We recently upgraded from SQL 2K to SQL 2005 on WINDOWS 2003 Server. The entire upgrade process has run pretty smoothly aside from encryption.
We have ASP.NET 1.x apps that connect to an internal server but fail when encrypted. We have tried all of the steps available on the MS KB and still have not resolved the issue.
On http://msdn2.microsoft.com/en-us/library/ms191192.aspx, we find the following:
To configure the server to accept encrypted connections
1. In SQL Server Configuration Manager, expand SQL Server 2005 Network Configuration, right-click Protocols for <server instance>, and then selectProperties.
2. In the Protocols for<instance name> Properties dialog box, on the Certificate tab, select the desired certificate from the drop down for the Certificate box, and then click OK.
3. On the Flags tab, in the ForceEncryption box, select Yes, and then click OK to close the dialog box.
4. Restart the SQL Server service.
The problem is when we get to step 2, there are NO certificates available in the dropdown box.
This problem is solely the result of installing SQL Server 2005. The apps were successfully encrypting data connections prior to the install. All internal and non-encrypted connections work correctly and no other changes were made to the box aside from the SQL 2005 upgrade.
This issue has remained unresolved for almost two weeks now.... We have tried the KB articles many times over, none of which have made a difference. We have also created certificates many times over, checking Server Authentication in the EKU section and making sure the cert has a private key. The expiration date on the certs we create are in 2009 and are named using the FQDN. Clearly, I'm no certificate expert, but there has got to be something we're missing with the certificates since every other app connection works like it should
What can I do to get certificates to appear in the Certificate tab of the Properties dialog box? What else would you recommend?
Many thanks.
-Matt
Orcabbelle,
To help you troubleshoot this issue, I need you to share some more info,
(1) ERRORLOG of the SQL Server,
You should be able to find it under place similar to
'C:\Program Files\Microsoft SQL Server\MSSQL.XX\MSSQL\LOG\ERRORLOG''
Extract specific lines that show certificate if any.
(2) Run certutil.exe as
certutil.exe -v -store my.
pasted the output w.r.t the cert that you have just installed here.
Generally, there is also way to modify the registry value to force server encryption. But it is not recommended because it is errorprone. Let me know if you want to do that to get you problem solved faster.
|||Nan,There is only one relevant line I can find in the errorlog:
2006-04-04 11:11:03.59 Server A self-generated certificate was successfully loaded for encryption.
Not sure what this means or where I can find the self-generated cert. Is it the cert in with the data files?
As for #2, we ran the cmd line you suggested and everything looks good aside from:
CERT_ARCHIVED_PROP_ID(19):
3988ce37fa66df4b79d75bb63c7f8d0d_c381bd65-7971-4be2-9c1b-aef143aed909
ERROR: Certificate public key does NOT match stored keyset
|||
So the certificate you are using is corrupted somehow.
One possible reason is that you have a previous installed certificate, and the newly installed certificate does not have permission to over write the private key of the old one. I am assuming you do have a certificates that have both public key and private key is expotable.
Try to clean up your certificate store using MMC. that have same FQDN as this new one has. Then install the certificate in the local store. Try to run as local admin to clean/install the certificate.
Let me know what you get.
Selfsigned certificate is newly added feature in SS2k5 that help to encrypt even user does not have certificate. the self signed certificate is stored in master database. It is briefly mentioned here. As result, it is possible to force encryption without provisioning a valid certificate in SS2k5
http://blogs.msdn.com/sql_protocols/archive/2005/10/04/476705.aspx
|||We cleaned up our MMC and our new cert seems to be fine (not corrupted). We want to use our cert, not the self-signed cert.As soon as we upgraded, all of our ASP.NET apps that were using Encrypt=True in their connection strings simply stopped working. Nothing else on either box, the server or the client, was changed. We changed the connection string to Encrypt=False and the apps work like they should, aside from not being encrypted. What could cause this?
We have tried just about every MS KB article and recommendation we can find. We created new certificates using a CA server. We made sure they have had private keys and allowed server authentication. Force protocol encryption is set to YES on the server.
We have never been able to get the certificate we create to appear in the list. We are logging on the server as the domain admin and starting the service under the same account.
Any ideas why we can't see the cert in the list or why the encryption failed after a SQL upgrade?
Many thanks!
|||
First, Your sql service should run under admin account, otherwise it has no permissing to the private key and thus the cert would not show up in the drop down list of SSCM, visit KB article for more detail:
http://support.microsoft.com/?kbid=900495.
Secondly, SQL Server 2005 has a new kind cert called self-signed certificate, which means when server can not find a good cert to load in the cert store, it will load such a self-signed cert. the encryption failure you saw is due to the reason that your server might load a self-signed cert(To verify this, you can go to server errorlog and see there is keyword "self-signed certificate"), and you forced client encryption through setting "Encrypt=True". If you already forced server encryption, the connection should be encrypted. And if you require forcing client encryption, you need to have server loading a certificate issued by a trusted CA.
Thirdly, please run "certutil.exe -v -store my", the tool would verify whether the certs you installed are valid;and try change sql service running under LocalSystem account, see whether any cert appear in the list.
|||The only thing that I can think of is that the certificates that you are generating and the one you used for SS2K do not meet the stricter requirements of SS2K5. Please have a look at this post to see what properties we require from a cert:
http://blogs.msdn.com/sql_protocols/archive/2005/12/30/508311.aspx.
Thanks,
Il-Sung.
Ok, IT has had a look at what you said and has had trouble with #4 found on the link you provided.
4) The Certficate’s key usage must include AT_KEYEXCHANGE property, which means Key Encipherment (a0)
They were able to find the Key Encipherment value but ours is not (a0), it's (f0). Not sure if this makes a difference or not.
From running "certutil.exe -v -store my":
Key Usage
Digital Signature, Non-Repudiation, Key Encipherment, Data Encipherment (f0)
|||
Hi Orcabelle,
Would you be able to try using a certificate that has a key usage of 0xA0 instead of 0xF0 and see if that makes a difference?
Also, could you indicate whether you're generating your own self-signed certificate? If so, what is the command that you're using the generate it. I believe it should be similar to:
makecert -r -pe -n "CN= MySQLServerName" -b 01/01/2000 -e 01/01/2036 -eku 1.3.6.1.5.5.7.3.1 -ss my -sr localMachine -sky exchange -sp "Microsoft RSA SChannel Cryptographic Provider" -sy 12
Thanks,
Il-Sung.
Now my question is, how do I change key usage from F to A?
|||
Hi Orcabelle,
I did some more looking into this and a key usage of f0 is perfectly fine. This is very perplexing problem. Could you please confirm that you're looking at the correct entry of the "certutil.exe -v -store my" output? i.e., you've properly correlated the cert hash with the one in the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLServer\SuperSocketNetLib\Certificate registry key?
Thanks,
Il-Sung.
We have the registry key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLServer\SuperSocketNetLib\Certificate, but it has no value. We also have the cert hash data, but what syntax should the key be in?
If the cert hash is supposed to automatically appear in this registry key, it does not. When the key value is changed to match the thumbprint (cert hash (sha1)) of the certificate then the MSSQL service will not start. When the value is blanked the service starts as it should.
|||Here is the error we recieve from asp.net 2.0 when Encrypt=True...
"System.Data.SqlClient.SqlException: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection connection, ConnectionState& originalState)
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(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
at Security.GetSystemData() in C:\Inetpub\screen\App_Code\Security.vb:line 109
at Security.SecurityCheck() in C:\Inetpub\screen\App_Code\Security.vb:line 94"
|||
Orcabelle,
(1) To configure the server hash in sqlserver 2005, you need to put the ascii form of the Thumbprint in to the registry mentioned above, without any spacec in between and before and after, for example "06065fbcc0fd1b45b04dd67fd236673a9aa6563c". If the certificate specified in the hash is not a valid one from the point a view of sql server, sql server will fail to started. If your case if later, you can look the errorlog, look for certificate related and find what is the root cause.
(2) If you force encryption in the client, asp2.0 in your case, and that the server happens to use a self-signed certificate or a certificate that is not trusted by the client, you will see the error "The certificate chain was issued by an authority that is not trusted". In such case, in order to still use encryption, you can add a connection string property,
"TrustServerCertificate = true", such that client does not validate certificate hierarchy.
http://msdn2.microsoft.com/en-US/library/system.data.sqlclient.sqlconnection.connectionstring(VS.80).aspx
SQL book online, "Using Encryption Without Validation" in SQL Server 2005 Books Online." also have related info.
|||So after speaking with Microsoft, we have determined the issue, but we're still awaiting a possible resolution.
The problem we've run into is caused by our using both an internal (abc.local) and external (xyz.com) domain. All this time, we had been using the FQDN for our external domain, but this wasn't being recognized by SQL Server 2005. As soon as we created a certificate using the FQDN for our internal domain, it was recognized.
That said, we still don't know for sure whether client based encryption is possible using SQL Server 2005 with valid but different named domains. We have tested and server based encryption does seem to work, but we still want to know why client based does not. It should be a two way street or so they say.
Anyway, thank you all for the help you provided. I will post Microsoft's response/recommendations once we hear from them.
Have a good day.
-Matt
No comments:
Post a Comment