0

I want to change my SQL Connections to use Windows authentication instead of database username and password. What changes do I need to do in the below code?

strSQL = ""
strSQL = strSQL & "OLEDB;" & vbCrLf
strSQL = strSQL & "Provider=SQLOLEDB.1;" & vbCrLf
strSQL = strSQL & "Integrated Security=SSPI;" & vbCrLf
strSQL = strSQL & "Initial Catalog=" & wshQuery.Range("B2").Value & ";" & vbCrLf
strSQL = strSQL & "Data Source=" & wshQuery.Range("A2").Value & ";" & vbCrLf
strSQL = strSQL & "Use Procedure for Prepare=1;" & vbCrLf
strSQL = strSQL & "Auto Translate=True;" & vbCrLf
strSQL = strSQL & "Packet Size=4096;" & vbCrLf
strSQL = strSQL & "Workstation ID=W-TPL-3275;" & vbCrLf
strSQL = strSQL & "Use Encryption for Data=False;" & vbCrLf
strSQL = strSQL & "Tag with column collation when possible=False"

strCon = strSQL

Thank you!

laylarenee
  • 3,276
  • 7
  • 32
  • 40
Becky
  • 115
  • 2
  • 18
  • Just put your title in google and you will find a myriad of answers, one should work like [this](http://stackoverflow.com/questions/1573725/excel-vba-connect-to-sql-with-a-trusted-connection-no-uid-pwd) – Scott Craner Oct 27 '15 at 17:30
  • I've looked at this one previously and it doesn't seem to work for me. – Becky Oct 28 '15 at 09:24

1 Answers1

0

See if this code works. Also every time you need a connection you can use this website

Option Explicit

Sub test()

    Dim strCon As String

    strCon = vbNullString
    strCon = strCon & "OLEDB;"
    strCon = strCon & "Provider=SQLOLEDB.1;"
    strCon = strCon & "Integrated Security=SSPI;"
    strCon = strCon & "Initial Catalog=" & wshQuery.Range("B2").Value & ";"
    strCon = strCon & "Data Source=" & wshQuery.Range("A2").Value & ";"
    strCon = strCon & "Use Procedure for Prepare=1;"
    strCon = strCon & "Auto Translate=True;"
    strCon = strCon & "Packet Size=4096;"
    strCon = strCon & "Workstation ID=W-TPL-3275;"
    strCon = strCon & "Use Encryption for Data=False;"
    strCon = strCon & "Tag with column collation when possible=False;"

    'Added this two
    strCon = strCon & "Uid=" & UserName & ";"
    strCon = strCon & "Pwd=" & UserPassword & ";"

End Sub

Thanks I hope it helps :)