May 28, 2006 at 9:41 am
Hello all, been wrestling with a problem on my laptop.
I have installed SQL Server 2005 Standard Edition.
When I try to connect using the Management studio, I get the following error:
*********************************************************
TITLE: Connect to Server
------------------------------
Cannot connect to BUONOASUS\SQL2005.
------------------------------
ADDITIONAL INFORMATION:
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.) (Microsoft SQL Server, Error: -2146893019)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-2146893019&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
**********************************************************
As I say, it has been an issue for a couple of days. I have looked at loads of Google results that mention certificates etc. but none of those paths have worked out.
My laptop is Windows XP Service Pack 2, loads of RAM.
SQL Server service runs as my own admin account ( I have also had it running as LocalSystem).
I also get the same message if I try to create a new Database from within Visual Studio 2005.
Any help on this?
May 31, 2006 at 8:00 am
This was removed by the editor as SPAM
January 21, 2007 at 8:44 pm
I know it has been over 6 months, but having experienced this error myself just recently, I wanted to share some answers. This appears to be a ADO.NET 2.0 error referencing the encryption status of your SQL Server. I would recommend you make sure that your SQL is set to NO ENCRYPTION. You will find then that the communication will work between your app and the SQL server.
otherwise, build into your connection string an encryption request.
While not necessarily directly answering the question, this link started me on the final answer: http://blogs.msdn.com/dataaccess/archive/2005/06/09/426957.aspx
Happy Trails.
January 23, 2007 at 1:52 pm
I am struggling with connection problems between my application and SQL 2005 as well, can you tell me where to set NO ENCRYPTION?
January 23, 2007 at 3:56 pm
Try this link
http://msdn2.microsoft.com/en-us/library/ms191192.aspx
you can also google for the encryption settings
Also, go into your SQL Server Configuration Manager
then go to SQL Server 2005 Network Connections
then go to Protocols for MSSQLServer
and make sure that your named pipes are enabled as well as tcp/ip
All of these might not be helpful if you are not getting the exact same error...so drop a line if it is not the exact same thing.
Later Days...
August 18, 2007 at 10:49 pm
i having the same problem..
any solution for this?
August 20, 2007 at 8:07 am
Have you tried turning on Pipes & TCP/IP in the Surface Area Configuration within SQL 2005
August 20, 2007 at 12:11 pm
Named Pipes is not strictly necessary for SQL Server 2005 to run. Unless, of course, you don't have any other protocols set up.
Also, sometimes weird errors happen when you don't have Remote Access turned on, but it's been a while since I installed stuff on my box, so I can't remember if this caused the same error you're having or not.
August 20, 2007 at 12:32 pm
HappyChai - please post the EXACT problem you are having including some specific details. Unless your error is exactly as it appears from the first posting, the answer is going to vary significantly. Please post your OS, your SQL version, and the specific error you see. I bet one of us will be able to tell you what you need to do.
Thanks for using SQL server central
February 14, 2008 at 9:19 am
Okay, I'm having a similar issue and I've done extensive research on this problem. First I'd like to say that if you have encryption functioning through your native client now and you need it to continue working as-is on the machine, DO NOT perform the tests I'm about to detail below. Use a different machine (perhaps a virtual server that you can revert back after the test). I have been unable to find out a way to reverse the state of the machine after I reach a certain point.
This is my scenario:
I need to connect to SQL Servers that are remotely hosted. They are a mix of both SQL Server 2000 and SQL Server 2005 servers. The communication channel will be restricted to only 1 IP. The communication must be secured using SSL. The internal communication between the application servers and the database servers at the hosted site must not be encrypted, so the encryption will be initiated on a per-client basis. The client machines should automatically trust the server certificate so the certificates don't have to be installed on the client machines.
So basically all hosted machines must be able to communicate back to one facility over the designated SQL Server port for centralized administration. This is why I'm testing SSL communication for SQL Server. I'm testing internally using my workstation against a dev server that hosts both SQL Server 2000 and SQL Server 2005 instances.
Scenario Givens:
This is what I found that must be true, so far, if you want to use SSL to secure a SQL Server connection (please correct if I made any mistakes):
This MSDN link lists 5 requirements at the bottom (http://msdn2.microsoft.com/en-us/library/ms189067.aspx)
1) The certificate must be in either the local computer certificate store or the current user certificate store.
2) The current system time must be after the Valid from property of the certificate and before the Valid to property of the certificate
3) 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)
4) The certificate must be created by using the KeySpec option of AT_KEYEXCHANGE. Usually, the certificate's key usage property (KEY_USAGE) will also include key encipherment (CERT_KEY_ENCIPHERMENT_KEY_USAGE)
5) 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
In test, I'm creating a self signed certificate using the SelfSSL.exe utility found in the IE6 ResKit using the following command, this command seems to generate a proper certificate that meets the 5 requirements above:
selfssl.exe /T /N:CN=server.host.com /K:1024 /V:365 /S:1 /P:443
The servers are not failover clusters. I was able to confirm the Enhanced Key Usage value by going to the details of the certificate in the Certificates MMC snapin. The certificate exists in the Trusted Root Certificate Authorities. The KeySpec option appears to be correct.
* Note - Tried using the MakeCert.exe program but it wasn't generating the private key in the certificate file for some reason. I wasn't able to figure out why, so that's why I chose to use SelfSSL.exe to generate the self-signed certificate. The command I used is:
makecert -r -pe -n "CN=server.host.com" -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 c:\Certificate.SelfSigned.cer
I was able to confirm the certificates by using the CheckCert.exe program found here: http://www.stevestechspot.com/ - found all the way at the bottom of the page.
Here are some more givens that I found to be true
- wildcard certificates definitely don't seem to work
- if you don't use the built-in self generated certificate for SQL Server 2005, you need to be logged in as the service account that runs the SQL Server service in order to be able to "see" and select the certificate in the SQL Server Configuration Manager
- I found this one article where the author claims that multi-protocol encryption will not work for named instances of SQL Server (http://www.sqlservercentral.com/articles/Administering/sqlserversecuritypart3/615/) I'm not sure if this is going to affect me, but it doesn't seem to be impacting me. Even my named instances seem to work fine so if someone could clarify this claim I'd appreciate it.
Okay, now that we have all of the givens, I'll go through the configuration I am using...
Server Configuration Steps:
1) Generate the certificate using SelfSSL.exe (see command above)
2) Import the certificate into Trusted Root Certificate Authorities using the Certificates MMC snap-in
3) Log on to the server as the SQL Server service account. Go to the SQL Server Configuration Manager. Expand SQL Server 2005 Network Configuration. Right click Protocols for instance. Click the Certificate tab. Select the self signed certificate from the drop down and click 'apply'
4) Restart SQL Server (both instances in my case since I'm using SQL Server 2000 / 2005
At this point you can check the logs to ensure the certificate was properly loaded. The server should be all set at this point.
Client Configuration Steps:
To see what's going on, you're going to need to download and install NetMon from Microsoft's site (http://www.microsoft.com/downloads/details.aspx?FamilyID=18b1d59d-f4d8-4213-8d17-2f6dde7d7aac&displaylang=en) I used the following filter (replace IPs and Ports with the proper ones for your machines)
TCP.Port == port AND IPv4.Address == x.x.x.x AND IPv4.Address == y.y.y.y
* Note - make sure when you set your filter you hit 'apply filter'. That got me a few times.
Test 1 (unsecured) - Native client settings
1) Open a connection through SQL Server Management Studio -> new query
2) Start the netmon trace
3) Run a command like 'SELECT * FROM sysdatabases'
4) Pause the netmon trace.
5) Repeat using SQLCmd
You will see at this point there will be a few lines captured, most of which are in clear text and you can read the query submitted
Test 1 (secured) - Native client settings
1) On the client, go to the SQL Server Configuration Manager
2) Right click the SQL Native Client Configuration and click Properties
3) Set Force Protocol Encryption = YES
4) Set Trust Server Certificate = YES
5) Now repeat the steps from the unsecure test above.
* Note - Every time you make a change to the settings you need to close and re-open the client tools
You will see now that the data should all be encrypted. GREAT! Now for the confusing part.
I started getting curious as to why we have Force Encryption in 3 different spots
1) SQL Server Configuration Manager
2) Connection Properties of a server registration inside the SQL Server Management Console
3) Cliconfg.exe
So I tried another test. I turned on the Force Encryption option in the cliconfg.exe and repeated test 1. WAIT! I'm getting the following error now:
TITLE: Connect to Server
------------------------------
Cannot connect to CGNY-DEVSQL01\SQL2000.
------------------------------
ADDITIONAL INFORMATION:
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.) (Microsoft SQL Server, Error: -2146893019)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-2146893019&LinkId=20476
But I have the Trust Server Certificate setting in the SQL Server Configuration Manager set to YES.
What's even more confusing is that the SQLCmd utility is working fine still. What's changed? I uncheck the box, restart the SQL Server Management Console. Same problem! I can't revert back to the way I had it before. It seems as if after making a change to the cliconfg.exe, SQL Server Management Console completely ignores the settings in the SQL Server Configuration Manager. I've tried all sorts of setting configurations after that and the only two it seems to look at are 2 and 3 from above. But now I have no way of automatically trusting server certificates... The only way I found to fix this problem is to import the certificate on the client, which breaks one of the original requirements listed at the top.
FYI, I did do a regmon trace and found the keys that are being set by the configuration tools. The settings are being set properly.
SQL Server 2005 Native Client
HKLM\Software\Microsoft\MSSQLServer\Client\SNI9.0\GeneralFlags\Flag1\Label = REG_SZ("Force Protocol Encryption")
HKLM\Software\Microsoft\MSSQLServer\Client\SNI9.0\GeneralFlags\Flag1\Value = REG_DWORD(1)
HKLM\Software\Microsoft\MSSQLServer\Client\SNI9.0\GeneralFlags\Flag2\Label = REG_SZ("Trusted Server Certificate")
HKLM\Software\Microsoft\MSSQLServer\Client\SNI9.0\GeneralFlags\Flag2\Value = REG_DWORD(1)
MDAC CliConfg
HKLM\Software\Microsoft\MSSQLServer\Client\SuperSocketNetLib\Encrypt = REG_DWORD(1)
Questions Left Unanswered
1) Why doesn't the self-signed certificate get generated properly using the MakeCert.exe command I used above?
2) Why doesn't it seem that anything needs to be set in the SQL Server 2000 instance in order for encryption to work? (whereas in SQL Server 2005 there is a location to select the certificate you want). I think I missed something here.
3) Why (and this is the big WHY) doesn't SQL Server Management Console seem to be affected by changes in the Native Client configuration on the client side after changes were made to the cliconfg.exe?
4) Are self-signed certificates safe for production scenarios if the traffic is restricted to specific IP addresses?
I haven't tested using the SQL 2000 clients because all of the clients that will be connecting to the remote servers will be using SQL Server 2005 client tools.
This is my first crack at encrypting SQL Server traffic, and I have almost no experience with certificates. This is just what I found after days of research and asking people, so if I'm grossly mistaken on something mentioned above please let me know and explain where I went wrong. Also, if I'm going about this the wrong way please let me know.
Thank you!
February 14, 2008 at 10:16 am
John,
Have you checked your desktop for bits of wings, legs, antenna? Sounds like there's a 'bug' in the system to me. @=)
Configuration Manager sets encryption so you can use it, not so you have to use it. Remember, encryption has a performance cost, hence the reason it's turned off by default. Then, after you're done turning it on, you have to let SQL know which specific servers you want to use it on. You could have zillions of servers and only want to use encryption on one. So, it makes sense that you have to set encryption in 2 places.
But what led you to believe that you had to set the Force Encryption property on Cliconfg.exe to get encryption to work to begin with? It looks like you got what you needed only setting it in the two above spots.
Someone did find the same issue you did. See the following URL. Unfortunately, there's no explanation as to why you shouldn't have it checked.
http://sqlforums.windowsitpro.com/web/forum/messageview.aspx?catid=60&threadid=47604&enterthread=y
Oh, wait. I just found the following on Microsoft's site. It's a little old, though. SQL 2000. Does this help? http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q318605
BTW, SQL 2000 isn't set up to natively generate certs and I believe it's using a different client completely as far as encryption settings go, so you probably couldn't do the same test against 2000.
If you can't find an answer to this issue, you might have to call Microsoft on this one. Either they did this deliberately or this is one of those many little things that they never considered.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply