Access linked tables can definitely use Windows Authentication, DSN or not. Each table in each Access DB can use its own authentication.

Pass-through queries, too, can use their own authentication.

Here's sample Access VBA code that can create DSN-less connections (i.e. all information stored in Access, no explicit ODBC DSN required), with options for Windows authentication.

Option Compare Database

Function AttachDSNLessTable(stDriver As String, stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, stApp As String, stEncrypt As String, stTrustServerCertificate As String, Optional stUsername As String, Optional stPassword As String)

' How to use, basic:

' Open the Access Database.

' If there's a security warning at the top, be sure this is really the DB you think it is, select it, and "Enable" functions.

' Alt-F11 to open up VBA

' In the upper left, right-click on the DB name and Insert a new module

' Copy and paste this entire function (AttachDSNLessTable) into the window

' Go to the Immediate window (Ctrl-G)

' copy and paste into the Immediate window:

' SQL 2012

' print AttachDSNLessTable("{SQL Server Native Client 11.0}","AccessTableName","SQLServerTableName","SQLServerInstance","SQLServerDatabase","Application Name","Yes","Yes","Optional SQL Server User Name","Optional SQL Server User Password")

' SQL 2008, 2008 R2

' print AttachDSNLessTable("{SQL Server Native Client 10.0}","AccessTableName","SQLServerTableName","SQLServerInstance","SQLServerDatabase","Application Name","Yes","Yes","Optional SQL Server User Name","Optional SQL Server User Password")

' SQL 2005

' print AttachDSNLessTable("{SQL Native Client}","AccessTableName","SQLServerTableName","SQLServerInstance","SQLServerDatabase","Application Name","Yes","Yes","Optional SQL Server User Name","Optional SQL Server User Password")

' update the arguments as required, (driver name, etc.) - see below for details on each one.

' Place the cursor on the print statement for each table you're linking and press Enter, one at a time - "True" means it worked, "False" means it failed.

' It's easiest to start at the top - the cursor will automatically (Access 2007) move through the list

' If you get a "False" response and an "Invalid Argument" error, note that there are undermined maximum lengths, possibly in the range of 270 characters overall.

' After you get all "True" responses, close VBA and close Access.

' When it asks if you want to save the new module, select "NO" - there's no need nor reason to preserve it, and if you

' save VBA you're likely to get more Microsoft security warnings.

'

'

' originally from http://support.microsoft.com/kb/892490

' 20120823 added stDriver, stApp, stEncrypt, stTrustServerCertificate as required parameters

' Encryption DOES NOT ACTUALLY work with the "SQL Server" client stDriver (preliminary indications per Wireshark,

' Win7 x64 to SQL Server 2005)

'//Name : AttachDSNLessTable

'//Purpose : Create a linked table to SQL Server without using a DSN

'//Parameters

'// stDriver: Name of the ODBC/OLEDB driver to use.

'// "SQL Server" is the old style driver.

'// "{SQL Native Client}" is SQL Server 2005's native client

'// "{SQL Server Native Client 10.0}" is SQL Server 2008 and 2008 R2's native client

'// "{SQL Server Native Client 11.0}" is SQL Server 2012's native client

'// 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

'// stApp: Text of the Application (APP=) string sent to the database server; make this descriptive (like the name of the access database)

'// DBA's will use this to help them identify where your SQL is coming from - "Microsoft Access" is not helpful, but

'// "MSAccess Finance Annual Report RT452" could be quite helpful, especially if it's from "FinanceAnnualReportRT452.mdb".

'// stEncrypt - "Yes" or "No" required. ERRORS out when "Yes" and stDriver = "SQL Server", since initial

'// testing shows encryption MAY NOT ACTUALLY work with the "SQL Server" stDriver (preliminary indications per Wireshark)

'// stTrustServerCertificate - "Yes" or "No" required.

'// If your SQL Server was not set up with a deliberately created certificate, this must be "Yes" if stEncrypt is "yes"

'// stUsername: Name of the SQL Server user who can connect to SQL Server, leave blank to use a Trusted Connection (Windows Authentication)

'// stPassword: SQL Server user password, leave blank to use a Trusted Connection (Windows Authentication)

'// Ex (SQL 2008 R2):

'// print AttachDSNLessTable("{SQL Server Native Client 10.0}","AccessTableName","SQLServerTableName","SQLServerInstance","SQLServerDatabase","Application Name","Yes","Yes","Optional SQL Server User Name","Optional SQL Server User Password")

On Error GoTo AttachDSNLessTable_Err

Dim td As TableDef

Dim stConnect As String

If stEncrypt = "Yes" And stDriver = "SQL Server" Then

MsgBox "Use stDriver {SQL Server Native Client 10.0} or {SQL Server Native Client 11.0} if you need encryption - packet sniffing shows this driver may not encrypt"

GoTo AttachDSNLessTable_Err

ElseIf stEncrypt = "Yes" Then

'// Valid option

ElseIf stEncrypt = "No" Then

'// Valid option

Else

MsgBox "Invalid stEncrypt value - enter Yes or No as a string"

GoTo AttachDSNLessTable_Err

End If

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=" & stDriver & ";APP=" & stApp & ";SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes;Encrypt=" & stEncrypt & ";TrustServerCertificate=" & stTrustServerCertificate

Else

'//WARNING: This will save the username and the password with the linked table information.

stConnect = "ODBC;DRIVER=" & stDriver & ";APP=" & stApp & ";SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword & ";Encrypt=" & stEncrypt & ";TrustServerCertificate=" & stTrustServerCertificate

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

Sub ListODBCTableProps()

' originally from http://accesstips.wordpress.com/2011/05/31/microsoft-access-dsn-less-linked-tables-tabledef-append-or-tabledef-refreshlink/

Dim db As DAO.Database

Dim tdf As DAO.TableDef

Dim prp As DAO.Property

Set db = CurrentDb

For Each tdf In db.TableDefs

If Left$(tdf.Connect, 5) = "ODBC;" Then

Debug.Print "----------------------------------------"

For Each prp In tdf.Properties

'Skip NameMap (dbLongBinary) and GUID (dbBinary) Properties here

If prp.Name <> "NameMap" And prp.Name <> "GUID" Then

Debug.Print prp.Name & ": " & prp.Value

End If

Next prp

End If

Next tdf

Set tdf = Nothing

Set db = Nothing

End Sub

'Don't forget to change the name of this procedure

Function GetCnnString() As String

' originally from http://accesstips.wordpress.com/2011/05/31/microsoft-access-dsn-less-linked-tables-tabledef-append-or-tabledef-refreshlink/

' WARNING WARNING WARNING the driver here is SQL Server 2008 and 2008 R2; see above for drivers for other versions.

GetCnnString = "ODBC;" _

& "DRIVER={SQL Server Native Client 10.0};" _

& "SERVER=MyServerName;" _

& "UID=MyUserName;" _

& "PWD=MyPassW0rd;" _

& "DATABASE=MySQLDatabaseName;" _

& "Encrypt=Yes"

End Function

Function SetDSNLessTablesNViews() As Boolean

' originally from http://accesstips.wordpress.com/2011/05/31/microsoft-access-dsn-less-linked-tables-tabledef-append-or-tabledef-refreshlink/

Dim db As DAO.Database

Dim tdf As DAO.TableDef

Dim strConnection As String

SetDSNLessTablesNViews = False 'Default Value

Set db = CurrentDb

'Use a Function to get the Connection string

'Note: In actual use I never use "Connection" in my Variables or Procedure names.

'I disguise them to make it hard for a hacker to use code to get my Connection string

strConnection = GetCnnString()

'Loop through the TableDefs Collection

For Each tdf In db.TableDefs

'Verify the table is an ODBC linked table

If Left$(tdf.Connect, 5) = "ODBC;" Then

'Skip System tables

If Left$(tdf.Name, 1) <> "~" Then

Set tdf = db.TableDefs(tdf.Name)

tdf.Connect = strConnection

If tdf.Attributes < 537001984 Then

tdf.Attributes = dbAttachSavePWD 'dbAttachSavePWD = 131072

End If

tdf.RefreshLink

End If

End If

Next tdf

SetDSNLessTablesNViews = True

Set tdf = Nothing

Set db = Nothing

End Function