CODEWITHSUNDEEP
sql-servervb6ado
Sai Manibalan
Sai Manibalan

Reputation: 95

How to secure connection between Vb6 application and mssql server?

I am working on a legacy application which uses sqloledb provider and activex data objects to connect to mssql database. Now I need to encrypt the connection between the application and server without force encryption option in sql server.

I have installed a self signed certificate in the sql server instance and tried putting the Encrypt=true and Trustservercertificate=true in connection string. But the connection is not encrypted.

I have tried using ODBC provider with ADO and while using encrypt=true and trustservercertificate=true, I am getting a SSL security error which opening the connection.

Please let me know how to establish a secure connection using ADO 2.8 library.

Private Sub Command1_Click()
    Dim sConnectionString As String
    Dim strSQLStmt As String

     '-- Build the connection string
     'sConnectionString = "UID=userid;PWD=password;Initial Catalog=EHSC_SYM_Kings_Development;Server=EHILP-257\MIB14;Provider=MSOLEDBSQL;Encrypt=YES;trustServerCertificate=YES"
     'sConnectionString = "Provider=sqloledb;Data Source=192.168.27.91\MIB14;Initial Catalog=EHSC_SYM_Kings_Development;User Id=userid;Password=password;Encrypt=YES;trustServerCertificate=YES"
     'sConnectionString = "driver={SQL Server};server=192.168.27.91\MIB14;user id=userid;password=password;Initial Catalog=EHSC_SYM_Kings_Development;Encrypt=Yes;trustServerCertificate=True"
     sConnectionString = "Provider=SQLNCLI11;Server=192.168.27.91\MIB14;Database=EHSC_SYM_Kings_Development;Uid=userid;Pwd=password;Encrypt=yes;trustServerCertificate=True"

     strSQLStmt = "select * from dbo.patient where pat_pid = '1001'"

    'DB WORK
    Dim db As New ADODB.Connection
    Dim cmd As New ADODB.Command
    Dim rs As New ADODB.Recordset
    Dim result As String

    db.ConnectionString = sConnectionString
    db.Open 'open connection

    With cmd
      .ActiveConnection = db
      .CommandText = strSQLStmt
      .CommandType = adCmdText
    End With

    With rs
      .CursorType = adOpenStatic
      .CursorLocation = adUseClient
      .LockType = adLockOptimistic
      .Open cmd
    End With

    If rs.EOF = False Then
        rs.MoveFirst
        Let result = rs.Fields(0)
    End If
    'close conns
    rs.Close
    db.Close
    Set db = Nothing
    Set cmd = Nothing
    Set rs = Nothing
End Sub

Upvotes: 3

Views: 3818

Answers (2)

TheLostBrain
TheLostBrain

Reputation: 649

Just went through this myself.

For your original connection string the keyword you're looking for is not "Encrypt=true" or "Encrypt=yes" like most of the internet would lead you to believe. It's "Use Encryption for Data=true".

So your connection string becomes:

sConnectionString = "Provider=SQLNCLI11;Server=192.168.27.91\MIB14;Database=EHSC_SYM_Kings_Development;Uid=userid;Pwd=password;Use Encryption for Data=true;trustServerCertificate=True"

The trustservercertificate=true is just for testing with self-signed certs... and exposing yourself to man-in-the-middle attacks ;)

For reference this was tested with: VB6 connecting to SQL Server 2016 using SQLNCLI11 provider via ADODB.

Upvotes: 1

Sai Manibalan
Sai Manibalan

Reputation: 95

Thank you all for your suggestions, I finally managed to make the connection secure by changing the driver to sqlserver native client 11(ODBC). Looks like sqloledb doesn't have support for tls. Changing the driver to ODBC doesn't seem to change the behaviour much. Rest of my code works fine without any changes

sConnectionString = "Driver={SQL Server Native Client 11.0};Server=192.168.27.91\MIB14;Database=xxxxxxxx;user id=xxxxxxx;password=xxxxxxxxx;Encrypt=yes;TrustServerCertificate=yes"

Upvotes: 1

Related Questions