DSN-less connection not storing password (1 Viewer)

[SteveVS]

I am aware of the security risk his type of solution exposes but due to reasons im forced to use this approach
I need to store the sql login credentials in the connection string
I have succesfully done this for a other application but it doesnt seem to work again

When the code runs it shows cleary that the connection string is set to use the sql credentials and not windows auth but the table shows that it using windows auth:banghead::banghead::banghead:

'//Name     :   AttachDSNLessTable
'//Purpose  :   Create a linked table to SQL Server without using a DSN
'//Parameters
'//     stLocalTableName: Name of the table that you are creating in the current database
'//     stRemoteTableName: Name of the table that you are linking to on the SQL Server database
'//     stServer: Name of the SQL Server that you are linking to
'//     stDatabase: Name of the SQL Server database that you are linking to
'//     stUsername: Name of the SQL Server user who can connect to SQL Server, leave blank to use a Trusted Connection
'//     stPassword: SQL Server user password
Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String)
    On Error GoTo AttachDSNLessTable_Err
    Dim td As TableDef
    Dim stConnect As String
    
    For Each td In CurrentDb.TableDefs
        If td.Name = stLocalTableName Then
            CurrentDb.TableDefs.Delete stLocalTableName
        End If
    Next
      
    If Len(stUsername) = 0 Then
        '//Use trusted authentication if stUsername is not supplied.
        stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"
    Else
        '//WARNING: This will save the username and the password with the linked table information.
        stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword
    End If
    Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)
    CurrentDb.TableDefs.Append td
    AttachDSNLessTable = True
    Exit Function

AttachDSNLessTable_Err:
    
    AttachDSNLessTable = False
    MsgBox "AttachDSNLessTable encountered an unexpected error: " & Err.Description

End Function

called using

call AttachDSNLessTable("BatchQIS_QTY","PartConfig.BatchQIS_QTY","SERVER","DB","USR","PWD")

 

[Frothingslosh]

That is the exact code I use, so the code itself is good.

My first suggestion would be to step through the procedure using a break point and F8, and see what value is actually getting assigned to stUsername.

 

[SteveVS]

That is what is the odd part.
As I run through the code stUsername is set "USR"
I can see this clearly within the variable and in the connection string

 

[Frothingslosh]

When you stepped through it, which branch of the IF...THEN loop did it follow?

And what does the connection string for the table show afterward when you hover your mouse over it?

A screenshot would be amazingly helpful if you can put one up - just black out or clip any confidential data.

EDIT: Ignore this - see the next post.

 

[Frothingslosh]

Hold it, hold it, hold it.

You said WINDOWS Authentication, not Server Authentication?

That's a setting that has to be changed in SQL Server - it was set when the server was first installed.

https://docs.microsoft.com/en-us/sq...ure-windows/change-server-authentication-mode

 

[SteveVS]

The settings are set to use it that way and has been from the beginning
But the problem still persist

The steps run through the part where it sets the credentials to use sql server but it stills default to windows auth

What confuses me is that this solution worked on another Access db using the same code and the same sql credentials and the same sql db

 

[Minty]

You haven't got a local SQL instance running by any chance? Like on a dev machine?