Encrypt connection to SQL Server 2005
-
Hi,
I need to encrypt the connection that my VB 6.0 app uses to connect to SQL Server 2005.
My app connects to SQL using SQL Server authentication.
Is there a problem with this?
Thank you
-
as i understand it, you should do this in two places;
you can make your server require encryption from everything connecting,a dnt he native client will handle it automatically:
SQL Server Configuration Manager and click properties for "SQL Native Client Configuration", if correct, you should be able to see two flags, one is "force client encryption", which you want to toggle to yes.
seperate from that, when you build an application with a connection string, one of the parameters is Encrypt=yes;
Driver={SQL Native Client};Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;Encrypt=yes;
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
But there is no problem with encrypting a connection because it uses SQL Server authentication instead of windowns authentication, correct?
Or i can only encrypt connection if they use windows authentication to connect to the server?
Thank you.
-
river1 (2/6/2010)
But there is no problem with encrypting a connection because it uses SQL Server authentication instead of windowns authentication, correct?Or i can only encrypt connection if they use windows authentication to connect to the server?
Thank you.
the encryption is for all the packets transferred to and from SQL Server, regardless of any authentication protocols or any other settings; so it's fast and cheap to just make the server require encryption.
you cannot be selective and make only one authentication type require encryption; about hte only option might be that you could make an application always use encryption even if the server did not require encryption by default, by adding to the connection string.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
No problem with encrypting communications to the server.
Another method is to establish an ssl certificate on both the client and the server.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
Hi,
I i'm very confused...
Are there any differences between Encrypting a connection to SQL Server and using SSL?
I thought the two where the same.
Can anyone explain to me the differences betweeen the two? can you give me a good link this?
Thank you very much
-
SSL is an https connection for a web page, using port 443 by default. in theory, i can go to your web page via https, but your web server goes to a different server for SQL data via the default of port 1443. so the data between your Web server and your SQL server is not encrypted. , but when the data is passed back to me via https SSL on the web connection, it is encrypted.
The same thing for when you connect directly to the SQL Server via SSMS or your application or whatever...the traffic passed back and forth is not encrypted.
That is the SQL specific info we were giving you, so you can easily force the traffic to and from the SQL server to be encrypted.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
ssl is added as an layer and is independent of sql server or https. it's just used as a component. just like if you add ssl to ftp you get sftp. or telnet -> stelnet etc.
How to enable SSL encryption for an instance of SQL Server by using Microsoft Management Console
-
But in my case, my apps are intranet apps made in Visual Basic. This are windows aplications they do not use the browser.
In this case, what i want is that because my VB aplication connects to SQL Server via SQL Server authentication, and because SQL Server authentication goes in plaintext by default, anyone with a sniffer or with profiler can see the passwords of the users that are hard coded inside my VB aplication.
what i want is that this informations can not be seen by sniffers of by profiler so i think i want to encrypt it.
What type of procedure would you advice?
Thank you.
-
yeah, encryption can be confusing; I didn't know SSL was an added layer; so I learned something today; (as usual here on SSC) Thanks Eduard!
river by changing that flag i mentioned before, "Force Protocol Encryption" to yes, you will achieve what you were after; traffic going to and from your SQL server will be encrypted. so your application will be more secure.
you can grab a scanner and see if you are not sure, or of course there's lots more information on MS or via google if you need deeper details to document how much more secure it will be. I know it's one thing to "say" it's more secure, and another to be able to show the chain of command.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
based on your info lowell's tip is a good idea.
there are many ways to rome and this is one of 'm.
-
Thank you very mutch.
A direct question is: will data not be seen if i use profiler? or a sniffer? after encrypting?
-
river1 (2/8/2010)
Thank you very mutch.A direct question is: will data not be seen if i use profiler? or a sniffer? after encrypting?
for profiler, only users who have ALTER TRACE permission can use profiler....that's pretty much just your server admins.
http://msdn.microsoft.com/en-us/library/ms187611.aspx
With that right, then they can run profiler and see the commands sent in the trace. you just need to check and make sure you do not have very many people with that right.
for a sniffer, all they will see is encrypted packets. that's what you were after, making your traffic more secure.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
river1 (2/8/2010)
A direct question is: will data not be seen if i use profiler? or a sniffer? after encrypting?a sniffer will have a problem. a profiler wont. the encryption is on the network transport part. not in the sql internals part where you look into with profiler.
this road to rome is the "prevent sniffing" road. there's also a road with "prevent a dba to see the actual data" road etc etc. "it depends" also rules here 🙂
encryption/dataprotection is not an easy subject. based on your question the KISS principle applies in my opinion (Keep It Simple). unless your doing a job for the department of defense. but then you need to upgrade your knowledge/expertise.
-
Ok that's what i will do (encrypt).
I was trying to do this (throught configutarion manager) and changed the value force protocol encryptation from NO, to yes.
Then i tested the connection, i have connected to Management studio and it can connect, even if i don't specify to use encryptation ....
It seems like SQL Server management studio can connect to SQL Server even if it don't sends data encryptated...
I have restarted the service, but it still is the same...
should this happen?
P.s - when i try to connnect throught management studio with encrypation it rases as error.
I can only connect when the connection is not encrypted.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply