Share via

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

VAer-4038 771 Reputation points
Jan 22, 2021, 10:30 PM

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.
14,491 questions
Office Visual Basic for Applications
Office Visual Basic for Applications
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Visual Basic for Applications: An implementation of Visual Basic that is built into Microsoft products.
1,501 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Erland Sommarskog 119.5K Reputation points MVP
    Jan 22, 2021, 11:06 PM

    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.

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.