Our organization has a shared Excel document that is used to keep track of inventory where our accounting system fails. That being said, I've been working on connecting the two. To do so, I've created several scripts and SQL queries to make this work. However, I'm struggling making the queries update on the end users computers. I keep getting the database login prompt. What do I need to do to get this working?

Key pieces of information:

1. I can login in the user into the SQL Server database using the credentials provided in the code using SSMS
2. When running it on my computer, the code attached works fine.
3. The SQL Server is on my computer
4. The SQL Server is TCP/IP enabled

Code:

Option Explicit
' Refreshing data from outside data sources
' Add the required references:
' - OLE Automation
' - Microsoft ActiveX Data Objects 2.8 Library
' - Microsoft Excel 16.0 Object Library
' - Microsoft Office 16.0 Object Library
' - Microsoft Outlook 16.0 Object Library
Sub RefreshQueries()
    Dim dbConnection As ADODB.Connection
    Dim dbServerName As String, qbDbName As String, aggDbName As String
    Dim connectionStr As String, qbDataArray(3), connectionName
    
    On Error GoTo ErrorHandler
    
    ' Database server details
        dbServerName = "111.11.0.1" ' not actual server ip
    
    ' Database names
        qbDbName = "QbData"
        aggDbName = "NonQBInventory"
    
    ' Connection string (WITH USERNAME AND PASSWORD)   ##########################################################
        connectionStr = "Provider=SQLOLEDB.1;" & _
                    "Data Source=" & dbServerName & ";" & _
                    "User ID=officeUser;" & _
                    "Password=passWord;" & _
                    "Connect Timeout=10;" ' Set the connection timeout value (in seconds)
                    
                    ' "Encrypt=yes;" & _
                    ' "TrustServerCertificate=no;" & _ (TRIED ADDING THIS TO CONNECTION STRING)
    
    ' Establish the database connection
        Set dbConnection = New ADODB.Connection
        dbConnection.Open connectionStr
    
    On Error GoTo 0
     
    ' Refresh queries in QbData database
        ' Fill qbDataArray
            qbDataArray(0) = "Query - GetCustomers SQL"
            qbDataArray(1) = "Query - GetItems SQL"
            qbDataArray(2) = "Query - GetOpenSOs SQL"
            qbDataArray(3) = "Query - GetUnitOfMeasures SQL"
    
        For i = 0 To UBound(qbDataArray)
            connectionName = qbDataArray(i)
            ThisWorkbook.Connections(connectionName).Refresh
        Next i
                
    ' Refresh queries in database2   ############    FOR LATER     #################################################
        'Call FillNonQbDataSqlArray
        
        'For i = 0 To UBound(nonQbData)
            'queryName = nonQbData(i)
            
            'With ThisWorkbook.Connections("Connection1") ' Replace "Connection1" with the actual name of the connection
            '    .OLEDBConnection.Connection = dbConnection
            '    .OLEDBConnection.CommandText = queryName ' Set the query name
            '    .Refresh
            'End With
        'Next i
    
    ' Close the database connection
        dbConnection.Close
        Set dbConnection = Nothing
    
    ' Refresh all other data connections in the workbook
        ThisWorkbook.RefreshAll
        Exit Sub
       
ErrorHandler:
    ' Handle the error here (e.g., display a message box or write to a log file)
        MsgBox "An error occurred: " & Err.Description, vbExclamation
        On Error Resume Next
        
        ' Close the database connection (if it is open)
        If Not dbConnection Is Nothing Then
        
            If dbConnection.State = adStateOpen Then
                dbConnection.Close
            End If
            
            Set dbConnection = Nothing
        End If
End Sub