Tell SQL Server 2008 R2 to use user name a password to avoid error 18452 using Access 2010 VBA and linked tables

I am working with SQL Server 2008 R2 and MS Access 2010. I have an .accdb with linked tables and use a login and password instead of windows authentication because we have several remote users. I have no trouble using this login and password using SSMS or my Access .adp project.

I have successfully created the DSN-less connection for my tables using the code from Doug Steele’s site http://www.accessmvp.com/djsteele/DSNLessLinks.html but I am having trouble with creating the cached connection from MS Office Blogs http://blogs.office.com/2011/04/08/power-tip-improve-the-security-of-database-connections/ .

I continue to get the following error:

Connection Failed: SQLState ;28000', Server Error 18452, 
The login is from an untrusted domain and cannot 
be used with Windows authentication.

This is at the point where the code tries to pass a test query to SQL:

Set rst = dbCurrent.OpenRecordset(strTable, dbOpenSnapshot)

And then when I click ok, I get the SQL Server Login screen with Use trusted connection checked (which I don't want) and a login ID auto-filled that is not the ID that I supplied via code.

So, number one, why is Access/SQL continually trying to connect using windows authentication when I don’t want it to. Is there a way to specify NOT to? Trusted_Connection = NO doesn’t seem to work.

Secondly (less importantly, but curious), the login ID is auto-filled but is not what I have for a login in my code. Why is SQL Server picking up a different login ID?

Note: If I uncheck Use trusted connection and fill in the correct login and password (which the VBA code is receiving correctly – I checked using debug.print to check my connection string), then the cached connection works. Therefore I also know my connection string is correct in the code.

I have double checked and my SQL Server is set to “mixed mode” to allow the login and password. (per post MS SQL Server (2008 R2) Error 18452 with Access 2010)

I do have “Native Client” installed (per post “How do I setup and ADODB connection to SQL Server 2008 inf Microsoft Access 2010)

My code:

Dim dbCurrent As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Dim strConnection As String

Dim strTable As String

strTable = "one of my linked tables"

strConnection = "ODBC;DRIVER={sql Server};" & _
                "DATABASE=MyDatabase;" & _
                "SERVER=MyServer;"

Set dbCurrent = DBEngine.Workspaces(0).Databases(0)
Set qdf = dbCurrent.CreateQueryDef("")

With qdf
    .Connect = strConnection & _
               "UID=" & strUserName & ";" & _
               "PWD=" & strPassword
    .SQL = "Select Current_User ();"

    Set rst = dbCurrent.OpenRecordset(strTable, dbOpenSnapshot)

End Wit
InitConnect = True

Solution

A few things:

First, ensure that your created SQL logon works. Note that you need BOTH a sql logon, and the ALSO a SQL user created for the given database (so don’t confuse the difference between a SQL server logon and that of SQL server user – this is COMMON confusion)

Use the SQL studio tools and disconnect from the database via your authenticated user and now re-connect via your new SQL logon. In other words be 100% sure that such SQL logons work from SSMS. Then (and ONLY then), attempt connecting from Access. So in SSMS test + try your SQL logon + password to ensure it works and lets you open/use the tables. Consider making the SQL logon a “owner” of the database and THEN create the user of the same name for that table.

I of course assume you set SQL server to mixed mode? And if the user(s) are not logged into your domain, then you could well have issues resolving the server name. (try using \192.0.0.50\sqlExpress or whatever your sql ip address and instance name is).

Next up:

Your “test” logon syntax is incorrect and will ALWAYS return false. Try typing your select string into sql server via SSMA and note how it does not work (that select will never work).

Remove the space and the () that you have.

I would just use:

.SQL = "Select Current_User;"

So your space + the () does not work and needs to be removed.

Also, I do suggest you include the Network-DBMSSOCN, as this means connect via TCP/IP. You likely are, but this enforces the issue.

Eg:

  dbCon = "ODBC;DRIVER="SQL Server;" & _
       "SERVER=" & ServerName & ";" & _
       "DATABASE=" & DataBaseName & ";" & _
       "UID=" & USERid & ";" & _
       "PWD=" & USERpw & ";" & _
       "Network=DBMSSOCN"

Once you ensured connecting via SQL logon as opposed to windows authentication works in SSMS, then fix your logon “select” command.

Also note that for the pass-word cache to work as a “general” rule the test logon string you use MUST be the same as the linked string + your uid/password. So the tabledef linked string does not have the uid/password.

Thus if the saved table link has things like "APP" etc, or other not required parts in the connection string, then your TEST logon connection string should have those SAME extra values along with the extra uid/logon. So if your test logon and test connection string is rather “different” then existing saved links, then the cache might have issues matching things up for the right table and server.

The code I use for test logon is:

Function TestLogin(strcon As String) As Boolean

  On Error GoTo TestError

  Dim dbs          As DAO.Database
  Dim qdf          As DAO.QueryDef

  Set dbs = CurrentDb()
  Set qdf = dbs.CreateQueryDef("")

   qdf.Connect = strcon

   qdf.ReturnsRecords = False

   'Any VALID SQL statement that runs on server will work below.
    qdf.SQL = "Select Current_User;"
   qdf.Execute

   TestLogin = True

   Exit Function

 TestError:
   TestLogin = False
   Exit Function

End Function

And code to create the string is:

Public Function dbCon(ServerName As String, _
                     DataBaseName As String, _
                     USERid As String, _
                     USERpw As String, _
                     Optional APP As String = "Office 2010", _
                     Optional WSID As String = "SC", _
                     Optional IntegratedSecurity As Boolean = False) As String

      ' returns a SQL server conneciton string
  If IntegratedSecurity = False Then
     dbCon = "ODBC;DRIVER={" & SQLDRIVER & "};" & _
             "SERVER=" & ServerName & ";" & _
             "DATABASE=" & DataBaseName & ";" & _
             "UID=" & USERid & ";" & _
             "PWD=" & USERpw & ";" & _
             "Network=DBMSSOCN"

  Else
     dbCon = "ODBC;DRIVER=" & SQLDRIVER & ";" & _
             "SERVER=" & ServerName & ";" & _
             "DATABASE=" & DataBaseName & ";" & _
             "APP=" & APP & ";" & _
             "WSID=" & WSID & ";" & _
             "Network=DBMSSOCN" & ";" & _
             "Integrated Security= SSPI"
  End If


End Function

And my logon "test" is:

Public Sub Logon()

  ' this simply tests + logs in the user to the default database.
  ' once this occurs, then a odbc logon prompt for each linked table
  ' should not occur - this works WHEN the connection string used here
  ' matches the odbc connection string exaclty.

    Dim strcon     As String


   ' con string settings are:

   ' server , DataBaseName, User, Password, [Optional Application name], [Optional work station]
   ' last two optional are for sql performance tracing etc. - not required

   strcon = dbCon("albertkallal-pc\SQLEXPRESS", "MyTestDec222", "test", "test")

   Debug.Print TestLogin(strcon)


End Sub

I would run/test the above routines WITHOUT launch any Access forms etc. So just test/use the above code. You need to get your logon working and when the logon works, then the prompt will go away.