-
Dec 28th, 2019, 01:15 PM
#1
Thread Starter
Fanatic Member
Secure Connection to SQL Server
Hey people. I've got a cloud based virtual PC running SQL Server 2016. I want a nice secure connection over the internet to this database from inside a VB6 program.
This is my old connection string that works fine but I doubt it's actually secure.
"Driver={SQL Server};Server=xxx.xxx.xxx.xxx;Database=dbName;User Id=sa;Password=xxxxxxxxx"
I've made zero changes to my SQL Server but I changed my connection string as follows:
Driver={SQL Server Native Client 11.0};Server=xxx.xxx.xxx.xxx;Database=dbName;User Id=sa;Password=xxxxxxxxx;Encrypt=yes;TrustServerCertificate=yes
Note the use of "Encrypt=yes"
So here's my question - what the heck does this actually do in reality? Is my connection now magically encrypted and secured? How can I verify this.
Many thanks in advance and for the ultra-smart people in the audience, sorry for noobing out on you with this question. I really have no idea what I'm doing.
-
Dec 28th, 2019, 02:55 PM
#2
Thread Starter
Fanatic Member
Re: Secure Connection to SQL Server
Just a quick answer to my own question. I installed Wireshark and captured a session first with connection string 1 above (no encryption). Everything was in plain text and easily readable. I'm assuming anybody with access to my WiFi network would be able to capture the stream and pull out emails and passwords and whatever else.
Next, I did the exact same thing with connection string #2 above (encryption) and the wireshark session wasn't readable at all (at least to me).
So a preliminary answer to my question: Turning on Encryption seems to be more secure. Is it 100% secure? No idea. But it's sure better than nothing.
-
Dec 30th, 2019, 11:54 AM
#3
Hyperactive Member
Re: Secure Connection to SQL Server
Using trustServerCertificate=true should NOT be used in live environments. It's a developer option so you don't have to purchase expensive third party certs on your dev boxes (https://docs.microsoft.com/en-us/pre...ctedfrom=MSDN)
-
Jan 1st, 2020, 01:07 PM
#4
Thread Starter
Fanatic Member
Re: Secure Connection to SQL Server
I agree I need a certificate. To keep costs low I thought I'd go for a free LetsEncrypt certificate but it looks like you can't get a certificate based just on an IP address. I don't have a domain name for that computer. Not sure where to go from here...
-
Jan 1st, 2020, 01:29 PM
#5
Fanatic Member
Re: Secure Connection to SQL Server
I believe that you can use self signed certificates and also turn off TrustServerCertificate, but you have to add the self signed certificate manually to each client by using IE Options-->Contents-->Certificate-->Import.
The advantage of paid certificates is that you don't have to do the manual step above. The same method above can be used to digitally sign in-house software, and deploy it across a corporate network, so the user doesn't see unknown publisher, and if a policy is set to only run digitally signed software, then it works.
I also think there is away to edit security policies so it's not necessary to go to each computer and change the settings, but I don't know the details.
Edit: Here is how to add self signed certificates to multiple computers at once.
Last edited by qvb6; Jan 1st, 2020 at 01:41 PM.
-
Jan 1st, 2020, 01:40 PM
#6
Thread Starter
Fanatic Member
Re: Secure Connection to SQL Server
I do have a code signing certificate from Comodo. Not sure if it's of any use to secure a SQL server though. I think I need something called a DV certificate. I guess I can call Comodo for some tech support but I hate sounding like a complete nOOb. Which I am. Oh well...
-
Jan 2nd, 2020, 02:32 PM
#7
Hyperactive Member
Re: Secure Connection to SQL Server
if you don't need anonymous connections to the sql server you may be able to secure it with firewall rules and\or a vpn
-
Jan 2nd, 2020, 09:07 PM
#8
Thread Starter
Fanatic Member
Re: Secure Connection to SQL Server
Actually the firewall may be a big help. I'll look at it. One thing I want to be very careful of... it's a cloud based server. If I screw up the firewall rule I'll end up locking myself out and I won't have physical access to the machine. I've really got to get the firewall rule right the first time. Any tips?
-
Jan 3rd, 2020, 07:58 AM
#9
Re: Secure Connection to SQL Server
There is something in SQL Server 2016 and up called Always-Encrypted. That encrypts data end to end have you looked at that?
Sometimes the Programmer
Sometimes the DBA
Mazz1
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|