Login failed for user (SQL Server) via VBA (Excel) Macro

Question

I'm creating a macro in an Excel spreadsheet that will connect to an instance of SQL server using SQL Server Authentication. This is a recycled function I've written previously that I know works. I suspect it's either the way I've constructed the connection string for the SQL authentication, or the way the user is configured (although I have successfully logged into the server through SSMS with the user account so I know the account works).

Public Function GetConnection(ServerLocation As String, db As String) As ADODB.Connection
Dim ServerName As String, cn As ADODB.Connection, un As String, pw As String

un = "MyUser"
pw = "MyPassword"

ServerName = "MyServer"

If cn Is Nothing Then
    Set cn = New ADODB.Connection
    cn.ConnectionString = "Driver={SQL Server};Server=" & ServerName & ";Database=" & db & ";UID='" & un & "';Pwd=" & pw & ";"
    cn.CursorLocation = adUseClient
    cn.Open

When I run the macro I get

Login Failed for user "MyUser"

at the cn.Open line.

I've checked that remote logins are allowed, and both SQL Server and Windows Authentication modes are enabled. I've also recreated the user account just in case.

Is there an issue with the connection string or am I missing something on the server?

Many thanks!

Try with the SQL Server Native Client 11.0. That old SQL Server driver is, well, very old... confirm they exist in your ODBC admin first of course. — Jacob H
– Jacob H, Commented Jan 16, 2018 at 17:44
Here is what I usually use: Provider=SQLOLEDB;Data Source=" & ServerName & "; Initial Catalog=" & db & "; Integrated Security=SSPI; — braX
– braX, Commented Jan 16, 2018 at 17:44
@JacobH the reason I'm using the old old driver is because I've had some users without the native client driver on their machines. This one seemed to be availavle on all if not most. Is there a more up to date driver that is likely to be on the our network machines? — Andrew Williams
– Andrew Williams, Commented Jan 16, 2018 at 19:37
i dont see anything wrong in the connection string though. have you tried using sa or any other user? — Plagon
– Plagon, Commented Jan 16, 2018 at 21:17

Andrew Williams · Accepted Answer · 2018-01-16 21:22:36Z

1

I found the solution: the single quotes around the UID are not required. I spotted these after viewing the Log and noticing the way the user name was formatted. Without the additional single quotes the connection is successful and my procedure executes successfully.

My final connection string looks like this:

cn.ConnectionString = "Driver={SQL Server};Server=" & ServerName & ";Database=" & db & ";UID=" & un & ";Pwd=" & pw & ";"

answered Jan 16, 2018 at 21:22

Andrew Williams

652 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Login failed for user (SQL Server) via VBA (Excel) Macro

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related