Results 1 to 9 of 9

Thread: Secure Connection to SQL Server

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Oct 2005
    Posts
    558

    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.

  2. #2

    Thread Starter
    Fanatic Member
    Join Date
    Oct 2005
    Posts
    558

    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.

  3. #3
    Hyperactive Member
    Join Date
    Mar 2018
    Posts
    460

    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)

  4. #4

    Thread Starter
    Fanatic Member
    Join Date
    Oct 2005
    Posts
    558

    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...

  5. #5
    Fanatic Member
    Join Date
    Feb 2019
    Posts
    706

    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.

  6. #6

    Thread Starter
    Fanatic Member
    Join Date
    Oct 2005
    Posts
    558

    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...

  7. #7
    Hyperactive Member
    Join Date
    Mar 2018
    Posts
    460

    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

  8. #8

    Thread Starter
    Fanatic Member
    Join Date
    Oct 2005
    Posts
    558

    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?

  9. #9
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    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
  •  



Click Here to Expand Forum to Full Width