February 18, 2010 at 8:55 am
Hi,
I have some questions, can you please help?
Thank you in advance.
1) Isnt' there a possibility of not using certificates to implement the encryptation between my SQL Server 2005 and my vb apps?
If i change only the property "force encryption" on the server, from NO to YES and then on the connection string of my VB 6.0 Apps put like this:
"Provider=SQLNCLI;Server=fscg41;Database=SGCT;Uid=teste;Pwd=teste;Encrypt=yes;"
Do i still have to provide a ceritificate to SQL Server for the connection to be encrypted?
article:"If the server can not find a valid certificate, a self-signed certificate will be issued by the server to encrypt data communication between the server and client. The login packet will always be encrypted. Whether the data communication is encrypted depends on the configuration on both server and client. "
2) I have read that i should not enable the property "force encryption" on the server and on the client at the same time. Why? what happens when we do this?
3) What is the best type of encryptation in my case ? is it server encryptation or client specific encryptation?
I have read that i can encrypt the connection to SQL Server in two ways. One is to put the property "force encryption" =yes on the server and the other is to put the property "force encryption" =yes on the SQL Native Client configuration.
What i want is that no one can connect to the server if they are not using encrypation, so in my case is better to put encrypation on the server, correct?
4) If i put encryptation on a server by installing a certicate because i decided to use server encryptation instead of client specific encrypation, do i need to export the certificate that i installed on the SQL Server server to each client computer that runs my vb app (that uses the connection string above) and then install the certificate on each client computer? or the certificate will only be installed on the server?
5) When a server (SQL Server 2005 server) is configured with SSL at the server level, it will only accept encrypted connections? even from SQL Server client tools (Management studio)?
6) As you noticed above , my connection string uses SQL Server Authentication instead of windows authentication , can i have some problems because of this? or the encryptation as nothing to deal with the method that the apps are connecting?
Thank you very mutch,
Pedro
P.s - I want to use Encryptation between the SQL Server 2005 server and my VB 6.0 apps because they will use SQL Server authentication in the connection string to connect to the SQL Server.
If i don't use it, then the data will be sent throught the network (intranet) in clear text and they can see (throught sniffer) the user and password that my app used to connected with. correct?
If this is not true, then i do not need encryptation....
February 18, 2010 at 9:46 am
I'm glad you posted, because I've been researching this very issue and would like more information. Here's what I know, based on lots of research:
1) Yes, you don't have to use a certificate. SQL Server will use a self signed certificate, based on that article you quoted and my own testing
2) I don't know what happens when you enable both server and client, but BOL says not to (as you stated)
3) The "best" type of encryption is supposed to be using an outside authority certificate, as with the self signed cert there's the possibility for "man in the middle" attacks. I've yet to verify this (my testing plan is outlined at the bottom of the post)
4) Yes, you'll need to export the certificate to all clients that connect to SQL Server
5) Yes
6) There will be no problems using SQL Server or Windows authentication. SQL Server authentication is always encrypted (based on the article you quoted, among others) even when the payload is not encrypted.
My tests:
Install Wireshark, start sniffing packets from my machine to my server. I filtered on port 1433.
Without "Enforce Encryption" at the server level, the authentication handshake was encrypted but the payload (my SQL statements and data sent) was not.
Enabled "Force Encryption" at the server level, not using an outside certificate and saw that the payload was now encrypted (or at least could not be seen thru Wireshark). I tested using SSMS and an ODBC connection (both SQL Server and SQL Server Native Client) from Access.
I asked about the difference between the client and server "Force Encryption" settings here:
http://www.sqlservercentral.com/Forums/Topic866163-146-1.aspx
Conclusion:
As far as I can tell, simply setting the "Force Encryption" at the server level in SQL Server Configuration Manager will encrypt your traffic. Your connection string will need the "Encrypt=Yes" setting, but SSMS and ODBC connections do not.
Sorry for the long post. Experts, please correct me if I'm wrong.
TIA
----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------
February 19, 2010 at 4:07 am
Thank you very much for your reply.
I have two questions about your reply.
The first one is:
this is my connection string inside my vb 6.0 app.
Provider=SQLNCLI;Server=fscg41;Database=SGCT;Uid=teste;Pwd=teste;Encrypt=yes;"
You say that i don't need to encrypt connections so that the user and pwd that the application sends to server (SQL Server 2005) can not be seen.
Are you certain of that? the app sends the data in clear text i think, so, if anyone uses a sniffer they can view the user and password that my app as sent to the SQL Server , am i correct?
Second question is:
You say that the SQL Server don't need any certificate to encrypt connections based on your tests. Do you mean NO certificate at all? you do not select a certificate from the certification tab and associate it with SQL Server?
Don't even need to create a certificate authority throught windows?
Thank you.
P.s - i have posted on Paul Randal blog this question about if it is possible to encrypt connections between my client apps and the SQL Server without using any type of certificate. I will post when he reply.
Also, i will start classes (course) about windows certificates in next tuesday, when it finnish i will post some notes too.
Thank you once again, and hope that some experts can validate this.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply