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

i working sql server 2008 r2 , ms access 2010. have .accdb linked tables , use login , password instead of windows authentication because have several remote users. have no trouble using login , password using ssms or access .adp project.

i have created dsn-less connection tables using code doug steele’s site http://www.accessmvp.com/djsteele/dsnlesslinks.html having trouble creating cached connection ms office blogs http://blogs.office.com/2011/04/08/power-tip-improve-the-security-of-database-connections/ .

i continue following error:

connection failed: sqlstate ;28000', server error 18452,  login untrusted domain , cannot  used windows authentication. 

this @ point code tries pass test query sql:

set rst = dbcurrent.openrecordset(strtable, dbopensnapshot) 

and when click ok, sql server login screen use trusted connection checked (which don't want) , login id auto-filled not id supplied via code.

so, number one, why access/sql continually trying connect using windows authentication when don’t want to. there way specify not to? trusted_connection = no doesn’t seem work.

secondly (less importantly, curious), login id auto-filled not have login in code. why sql server picking different login id?

note: if uncheck use trusted connection , fill in correct login , password (which vba code receiving correctly – checked using debug.print check connection string), cached connection works. therefore know connection string correct in code.

i have double checked , sql server set “mixed mode” allow login , password. (per post ms sql server (2008 r2) error 18452 access 2010)

i have “native client” installed (per post “how setup , adodb connection sql server 2008 inf microsoft access 2010)

my code:

dim dbcurrent dao.database dim qdf dao.querydef dim rst dao.recordset dim strconnection string  dim strtable string  strtable = "one of linked tables"  strconnection = "odbc;driver={sql server};" & _                 "database=mydatabase;" & _                 "server=myserver;"  set dbcurrent = dbengine.workspaces(0).databases(0) set qdf = dbcurrent.createquerydef("")  qdf     .connect = strconnection & _                "uid=" & strusername & ";" & _                "pwd=" & strpassword     .sql = "select current_user ();"      set rst = dbcurrent.openrecordset(strtable, dbopensnapshot)  end wit initconnect = true 

a few things:

first, ensure created sql logon works. note need both sql logon, , sql user created given database (so don’t confuse difference between sql server logon , of sql server user – common confusion)

use sql studio tools , disconnect database via authenticated user , re-connect via new sql logon. in other words 100% sure such sql logons work ssms. (and then), attempt connecting access. in ssms test + try sql logon + password ensure works , lets open/use tables. consider making sql logon “owner” of database , create user of same name table.

i of course assume set sql server mixed mode? , if user(s) not logged domain, have issues resolving server name. (try using \192.0.0.50\sqlexpress or whatever sql ip address , instance name is).

next up:

your “test” logon syntax incorrect , return false. try typing select string sql server via ssma , note how not work (that select never work).

remove space , () have.

i use:

.sql = "select current_user;" 

so space + () not work , needs removed.

also, suggest include network-dbmssocn, means connect via tcp/ip. are, enforces issue.

eg:

  dbcon = "odbc;driver="sql server;" & _        "server=" & servername & ";" & _        "database=" & databasename & ";" & _        "uid=" & userid & ";" & _        "pwd=" & userpw & ";" & _        "network=dbmssocn" 

once ensured connecting via sql logon opposed windows authentication works in ssms, fix logon “select” command.

also note pass-word cache work “general” rule test logon string use must same linked string + uid/password. tabledef linked string not have uid/password.

thus if saved table link has things "app" etc, or other not required parts in connection string, test logon connection string should have same values along uid/logon. if test logon , test connection string rather “different” existing saved links, cache might have issues matching things right table , server.

the code use test logon is:

function testlogin(strcon string) boolean    on error goto testerror    dim dbs          dao.database   dim qdf          dao.querydef    set dbs = currentdb()   set qdf = dbs.createquerydef("")     qdf.connect = strcon     qdf.returnsrecords = false     'any valid sql statement runs on server work below.     qdf.sql = "select current_user;"    qdf.execute     testlogin = true     exit function   testerror:    testlogin = false    exit function  end function 

and code create string is:

public function dbcon(servername string, _                      databasename string, _                      userid string, _                      userpw string, _                      optional app string = "office 2010", _                      optional wsid string = "sc", _                      optional integratedsecurity boolean = false) string        ' returns sql server conneciton string   if integratedsecurity = false      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 logon "test" is:

public sub logon()    ' tests + logs in user default database.   ' once occurs, odbc logon prompt each linked table   ' should not occur - works when connection string used here   ' matches odbc connection string exaclty.      dim strcon     string      ' con string settings are:     ' server , databasename, user, password, [optional application name], [optional work station]    ' last 2 optional sql performance tracing etc. - not required     strcon = dbcon("albertkallal-pc\sqlexpress", "mytestdec222", "test", "test")     debug.print testlogin(strcon)   end sub 

i run/test above routines without launch access forms etc. test/use above code. need logon working , when logon works, prompt go away.