I'm trying to connect to a SQL Server from multiple PCs in the same domain.
When using the following code:
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection 'Neue Verbindung initialisieren
'Verbindung einrichten'
conn.ConnectionString = "PERSIST SECURITY INFO=True;Provider=SQLOLEDB.1;Server=WWDDB;Database=01Projekt;User ID=XXX;Password=XXX;Trusted_Connection=True;Integrated Security=SSPI;"
conn.Open
conn.Open returns the error:
Error on login for the user 'XXXX'
The issue is because you are using a named user with Integrated Security. These two modes are incompatible.
Try removing Integrated Security=SSPI:
conn.ConnectionString = "PERSIST SECURITY INFO=True;Provider=SQLOLEDB.1;Server=WWDDB;Database=01Projekt;User ID=XXX;Password=XXX;Trusted_Connection=True;" conn.Open
Or the named user:
conn.ConnectionString = "PERSIST SECURITY INFO=True;Provider=SQLOLEDB.1;Server=WWDDB;Database=01Projekt;Trusted_Connection=True;Integrated Security=SSPI;" conn.Open
this is my snippet, try it :
Set cnConn = New ADODB.Connection
With cnConn
.Provider = "SQLOLEDB.1"
.CursorLocation = adUseClient
.ConnectionTimeout = 0
.Properties("Data Source").Value = ' serverName
.Properties("Password").Value = ' pswd
.Properties("User ID").Value = ' userName
.Properties("Initial Catalog").Value = ' init DB
.Open
End With
Integrated Security? Use one or the other