Adding SQL Server Credentials to ADO Connection-CodePudding

I have an Excel workbook that builds a bunch of SQL Update scripts, and then executes them in SQL Server.

I got assistance with the below VBA script. The below works fine if I am running it while logged in as the Admin user Windows. However, when running from a users workstation I run into issues.

The main issue seems to be the user id and password are incorrect. I am not sure where on the below I can add the system administrator (sa) user name and password for SQL Server. Please may I get some assistance.

My code:

Sub test()

Const SERVER = "SRV\ServerName"
Const DATABASE = "Test Database"

Dim fso As Object, ts As Object, ar
Dim ws As Worksheet
Dim iLastRow As Long, i As Long
Dim sql As String, timestamp As String
Dim Folder As String, SQLfile As String, LOGfile As String
Dim t0 As String: t0 = Timer

' query file and log filenames
timestamp = Format(Now, "YYYYMMDD_HHMMSS")
Folder = "\\SRV\Test Folder\"
SQLfile = Folder & timestamp & ".sql"
LOGfile = Folder & timestamp & ".log"

Set fso = CreateObject("Scripting.FileSystemObject")

' read data from sheet into array to build sql file
Set ws = ThisWorkbook.Sheets("UDF Update")
iLastRow = ws.Cells(Rows.Count, "N").End(xlUp).Row
If iLastRow = 1 Then
    MsgBox "No data in Column N", vbCritical
    Exit Sub
End If
ar = ws.Range("N2").Resize(iLastRow - 1).Value2

' connect to server and run query


    Dim sConn As String, conn, cmd, n As Long
    sConn = "Provider=SQLOLEDB;Server=" & SERVER & _
            ";Initial Catalog=" & DATABASE & _
            ";Trusted_Connection=yes;"

    ' open log file
    Set ts = fso.CreateTextFile(LOGfile)

    ' make connection
    Set conn = CreateObject("ADODB.Connection")
    conn.Open sConn

    ' execute sql statements
    Set cmd = CreateObject("ADODB.Command")
    With cmd
        .ActiveConnection = conn
        For i = 1 To UBound(ar)
            ts.writeLine ar(i, 1)
            .CommandText = ar(i, 1)
            .Execute
            
    On Error Resume Next
    Next
    End With
    ts.Close
    conn.Close
    MsgBox UBound(ar) & " SQL queries completed (ADODB)", vbInformation, Format(Timer - t0, "0.0 secs")


End Sub

CodePudding user response：

If you use Trusted_Connection=yes, the SQL server accepts/rejects you via Windows authentication. It seems that your admin account is accepted by the server while other accounts are not.
Either the other accounts are added to the database server by the database admin or you need to provide credentials and set Trusted_Connection=no (or omit it as that is the defaults)

sConn = "Provider=SQLOLEDB;Server=" & SERVER & _
        ";Initial Catalog=" & DATABASE & _
        ";Trusted_Connection=no" & _
        ";User ID=MyUserID;Password=MyPassword;"

See https://docs.microsoft.com/en-us/sql/ado/guide/appendixes/microsoft-ole-db-provider-for-sql-server?view=sql-server-ver15