Search code examples
sqlexcelvbaconnection

VBA Runtime Error when connection to SQL Database

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'

Solution

  • 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