CODEWITHSUNDEEP
sql-serversql-server-2005ms-accessvbams-access-2003
Bruno
Bruno

Reputation: 6449

MS Access connecting with Windows Username to SQL Server

Dim Cnxn As ADODB.Connection
Set Cnxn = New ADODB.Connection
With Cnxn
  .Provider = "MSDataShape"
  .Properties("Data Provider").Value = "SQLOLEDB"
  .Properties("Data Source").Value = dbserver
  .Properties("User ID").Value = Username
  .Properties("Password").Value = password
  .Properties("Initial Catalog").Value = dbname
  .CommandTimeout = 120
  .ConnectionTimeout = 120
  .Open
End With

Above is how I connect from MS Access to SQL Server where username is a default SQL username. However, I would like to connect with the Windows username but don't have the time to create 100's of usernames on the SQL Server, one for each Windows user. Is there a nice way of doing this? This is for auditing purposes so I know who did what.

Upvotes: 2

Views: 289

Answers (1)

Neil Knight
Neil Knight

Reputation: 48537

Remove the Properties UserID and Password, and replace with Integrated Security with a value of true.

.Properties("Integrated Security").Value = true

*Might be a capital T for true

Upvotes: 3

Related Questions