Excel VBA: SQL Connection Error (run-time error 2147217843 login failed"

VAer 756 Reputation points
2021-01-22T22:30:24.33+00:00

It is excel VBA issue.

If I use Sybase connection (another database from same screenshot of ODBC Data Source), and the code works fine.

But if I use the code for SQL Server, it returns error, see attached screenshot. I am sure that Server name and Database name are correct. Connection string looks correct too ( microsoft-odbc-driver-17-for-sql-server ). Did I use the correct connection string (standard security)? What could cause such error? Is it related to SQL database setting? Or some kind of Excel reference?

I am not an IT professional, so not sure why it has error. Is it because of database setting? Is it because it does not allow User/Password access? How does SQL server setting work?

Thanks.

Edit: is it related to Microsoft Office? My workplace microsoft office may be 32 bits (not sure), while the database is 64 bits? Not an IT, don't know much about it. The reason I say this: when I use Access > External Data > ODBC Database to connect to the database, it shows 32 bit for the database. But when I am ODBC Data Source, it shows 64 bits, since it is 64 bit laptop.

Edit 2: I can manually use excel to connect the database and copy data to excel, see screenshot. So I do have the access to database.

59648-runtime-error.jpg

59698-odbc2.jpg
59722-database.jpg
59731-excel.jpg 

Username = TextBoxUsername.Value  
Password = TextBoxPassword.Value  
  
Username = WorksheetFunction.Trim(Username)  
Password = WorksheetFunction.Trim(Password)  
  
  
'This is the connection string for MS SQL, which does not work.  
ConnectionString = "Driver={ODBC Driver 17 for SQL Server};Server=myserver;Database=mydatabase;UID=" & Username & ";PWD=" & Password & ";"  
  
'Below code is for Sybase database, which works.  
'ConnectionString = "Driver={Adaptive Server Enterprise};server=myserver;port=20025;db=mydatabase;uid=" & Username & ";pwd=" & Password & ";"  
  
  
'set logon details:  
Set Cn = CreateObject("ADODB.Connection")  
  
  
'Connect to Database  
Cn.Open ConnectionString  
  
  
Unload Me  
Form2.Show
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,810 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 101.8K Reputation points MVP
    2021-01-22T23:06:24.693+00:00

    The error message means that you were able to log on to SQL Server, but you were not able to connect to the database you specified in the connection string. This could be one of:

    1. The database does not exist.
    2. The database is not available (offline, single_user mode etc.)
    3. Your login does not have access to the database.