Force MS Access ODBC Connection to use SQL Server Authentication

Question

I'm using the following code that is run from my Autoexec macro to make the ODBC connection.

'Name     :   CreateDSNConnection
'Purpose  :   Create a DSN to link tables to SQL Server
'Parameters
'     sServer: Name of SQL Server that you are linking to
'     sDatabase: Name of the SQL Server database that you are linking to
'     sUsername: Name of the SQL Server user who can connect to SQL Server, leave blank to use a Trusted Connection
'     sPassword: SQL Server user password
Public Function CreateDSNConnection(sServer As String, sDatabase As String, sUsername As String, sPassword As String) As Boolean
Dim sConnect As String
On Error GoTo CreateDSNConnection_Err

  If Len(sUsername) = 0 Then
    '//Use trusted authentication if stUsername is not supplied.
    sConnect = "Description=DBTraining" & vbCr & "SERVER=" & sServer & vbCr & "DATABASE=" & sDatabase & vbCr & "Trusted_Connection=Yes"
  Else
    sConnect = "Description=DBTraining" & vbCr & "SERVER=" & sServer & vbCr & "DATABASE=" & sDatabase
  End If

  DBEngine.RegisterDatabase "DBTraining", "SQL Server", True, sConnect

  'Add error checking.
  CreateDSNConnection = True
  Debug.Print "Connection made"
  Exit Function

CreateDSNConnection_Err:
  CreateDSNConnection = False
  MsgBox "CreateDSNConnection encountered an unexpected error: " & Err.Description, vbCritical, "Unexpected Error!"

End Function

Pretty standard stuff...but it seems that the ODBC connection is always setup as using Windows auth and I need it setup to use SQL Server auth. I've tried dropping in the UID= and PWD=, but it still tries to use Windows.

Wayne G. Dunn · Accepted Answer · 2015-12-23 19:16:20Z

1

Here is a trusted connection from Access to SQL Server:

strConnectString = "Driver={SQL Server};" & _
                   "Server=" & strDSN & ";" & _
                   "Database=" & strDatabase & ";" & _
                    "Trusted_Connection=yes"

Or for SQL Server Authentication:

strConnectString = "Driver={SQL Server};" & _
                   "Server=" & strDSN & ";" & _
                   "Database=" & strDatabase & ";" & _
                   "User Id=,myUID>;Password=<MyPWD>"

answered Dec 23, 2015 at 19:16

Wayne G. Dunn

4,3322 gold badges15 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Andre Over a year ago

@Rdster: you can also try adding an explicit ;Trusted_Connection=No

Rdster · Accepted Answer · 2015-12-23 22:09:42Z

0

What ended up working for me was starting it over from scratch.

I blew out the existing UserDSN from the ODBC connection.
Then opened my DB and ran the CreateDSNConnection code
That put the UserDSN back in, and was set to use SQL Server Authentication, but was using my Windows login
Deleted all linked tables and views
Recreated the links for tables and views using the Generic SQL login account and checked the Save password option
Compacted & Repaired
Sent to a user to test...it worked

I had tried every one of these steps multiple times...except for the first one. I'm guessing that there was something corrupted in the UserDSN file??? I have no idea. But deleting it and starting over completely resolved the issue.

answered Dec 23, 2015 at 22:09

Rdster

1,8722 gold badges19 silver badges30 bronze badges

1 Comment

Albert D. Kallal Over a year ago

What occurs is any table you open, then Access will cache the information. From that point on EVEN a re-link will re-use that cached information. The VERY important concept is thus to ensure that when you launch Access that no table EVER opened or used BEFORE you re-link. So no startup forms etc. can be allowed. In fact this effect can be put to good use since you do NOT need to include UID/PASSWORD in the SQL link string. How this works is explained here: blogs.office.com/b/microsoft-access/archive/2011/04/08/…

gofr1 · Accepted Answer · 2015-12-24 13:12:16Z

0

Dim CurConn As ADODB.Connection
Set CurConn = New ADODB.Connection
CurConn.Open "Provider=myProvider;" & _
             "Server=myServer;" & _
             "Database=myDatabase;" & _
             "Trusted_Connection=no;" & _
             "User Id=myUsername;" & _
             "Password=myPassword;"

This, how I do it in Access VBA.

answered Dec 24, 2015 at 13:12

gofr1

16k11 gold badges47 silver badges56 bronze badges

Collectives™ on Stack Overflow

Force MS Access ODBC Connection to use SQL Server Authentication

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related