MSSC_support
asked on
VBA connection string problem
I have an Office 365 Excel VBA program which connects to a local Sql Server Express 2017 to run a stored procedure. Sql Server is set to Sql Server and Windows authentication mode.
I have a connection string in the VBA code:
"Provider=SQLOLEDB;Data Source=" & server & ";Initial Catalog=" & database & ";Trusted_Connection=Yes"
... which works perfectly.
When I set the connection string to a Sql Server user:
connString = "Provider=SQLOLEDB;Data Source=" & server & ";Initial Catalog=" & database & ";User ID=" & uid & ";Password =" & password
... I get the error message:
Login failed for user '3'
The Sql Server user (PFreeman) and password I specified has db owner privileges.
There is user or login named 3 in Sql Server.
Can you suggest why the VBA/ADO or whatever is sending the user 3 to Sql Server or what might be the problem with the connection string? I have tried every permutation to the connection string I can think of with no success.
Thanks
I have a connection string in the VBA code:
"Provider=SQLOLEDB;Data Source=" & server & ";Initial Catalog=" & database & ";Trusted_Connection=Yes"
... which works perfectly.
When I set the connection string to a Sql Server user:
connString = "Provider=SQLOLEDB;Data Source=" & server & ";Initial Catalog=" & database & ";User ID=" & uid & ";Password =" & password
... I get the error message:
Login failed for user '3'
The Sql Server user (PFreeman) and password I specified has db owner privileges.
There is user or login named 3 in Sql Server.
Can you suggest why the VBA/ADO or whatever is sending the user 3 to Sql Server or what might be the problem with the connection string? I have tried every permutation to the connection string I can think of with no success.
Thanks
Be sure your credentials are correct. Try logging into SQL Server Management Studio using the same credentials.
According to the error message, the User Id parameter is the user name, not a number.
Also check whether SQL Server authentication is enabled.
Then you should check the installed drivers, I prefer ODBC Driver 17 for SQL Server or SQL Server Native Client 11.0.
Also check whether SQL Server authentication is enabled.
Then you should check the installed drivers, I prefer ODBC Driver 17 for SQL Server or SQL Server Native Client 11.0.
ASKER
I can successfully log onto SSMS with the supplied credentials
Here is a code fragment...
' Set the connection variables
server = "PFFDEV\SQLEXPRESS"
database = "SccShop42"
uid = "PFreeman"
password = "PolymathUniverse"
'connString = "Provider=SQLOLEDB;Data Source=" & server & ";Initial Catalog=" & database & ";User ID=" & uid & ";Password =" & password
connString = "Provider=SQLOLEDB;Data Source=" & server & ";Initial Catalog=" & database & ";Trusted_Connection=Yes"
On Error GoTo DatabaseError
If conn.State <> adStateOpen Then
conn.Open connString, adOpenStatic
End If
If conn.State = adStateOpen Then
With cmd
.ActiveConnection = conn
.CommandText = "dbo.SccShopSales"
.CommandType = adCmdStoredProc
.NamedParameters = True
.Parameters.Append .CreateParameter("@startDa te", adDBDate, adParamInput, , startDate)
.Parameters.Append .CreateParameter("@endDate ", adDBDate, adParamInput, , endDate)
End With
Set rs = cmd.Execute()
rs.Close
conn.Close
End If
Here is a code fragment...
' Set the connection variables
server = "PFFDEV\SQLEXPRESS"
database = "SccShop42"
uid = "PFreeman"
password = "PolymathUniverse"
'connString = "Provider=SQLOLEDB;Data Source=" & server & ";Initial Catalog=" & database & ";User ID=" & uid & ";Password =" & password
connString = "Provider=SQLOLEDB;Data Source=" & server & ";Initial Catalog=" & database & ";Trusted_Connection=Yes"
On Error GoTo DatabaseError
If conn.State <> adStateOpen Then
conn.Open connString, adOpenStatic
End If
If conn.State = adStateOpen Then
With cmd
.ActiveConnection = conn
.CommandText = "dbo.SccShopSales"
.CommandType = adCmdStoredProc
.NamedParameters = True
.Parameters.Append .CreateParameter("@startDa
.Parameters.Append .CreateParameter("@endDate
End With
Set rs = cmd.Execute()
rs.Close
conn.Close
End If
I would use the MSOLEDBSQL provider.
SQLOLEDB is old and depreciated:
https://docs.microsoft.com/en-us/sql/connect/oledb/oledb-driver-for-sql-server?view=sql-server-ver15
SQLOLEDB may not work with SQL 2017.
Jim.
ASKER
I created a Sql Server login and user named 3 as listed in the earlier error message...
uid = "3"
password = "PolymathUniverse"
connString = "Provider=SQLOLEDB;Data Source=" & server & ";Initial Catalog=" & database & ";User ID=" & uid & ";Password =" & password
That worked.
It must be that Excel or ADO is rewriting the user name.
I'll try the suggested drivers next.
uid = "3"
password = "PolymathUniverse"
connString = "Provider=SQLOLEDB;Data Source=" & server & ";Initial Catalog=" & database & ";User ID=" & uid & ";Password =" & password
That worked.
It must be that Excel or ADO is rewriting the user name.
I'll try the suggested drivers next.
ASKER
I changed the connection to use:
Provider=SQL Server Native Client 11.0
and it all works as it should.
Thank you ste5an and Jim Dettman
Provider=SQL Server Native Client 11.0
and it all works as it should.
Thank you ste5an and Jim Dettman
ASKER
The most current driver appears to be MSOLEDBSQL
I tried that and it works as well, so will stay with it.
I tried that and it works as well, so will stay with it.
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Your pasted code didn't show us how you arrived with the variable "uid"
Debug it yourself, or provide more code