Excel VBA - Connection to SQL database fails

Question

I've got this code I'm trying to use to export data from Excel to an SQL Database and I'm receiving this error when I try to open the connection.

[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for the user 's_accounting'

This is the code I'm trying to use(variables already Dimmed of course)

ServerName = "192.168.168.34"
DatabaseName = "Accountingnumbers"
TableName = "Projects"
UserID = "s_accounting"
Password = "password123"

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset    
Set cn = New ADODB.Connection

cn.Open "Driver={SQL Server};Server=" & ServerName & ";Database=" & DatabaseName & _
    ";Uid=" & UserID & ";Pwd=" & Password & ";"

Note that I've renamed some sensitive stuff in there. Also, I've set the permissions on the database using this SQL Query:

use [db]
go
create user [myDomain\Groupname] for login [myDomain\Groupname]
go
grant select on schema::dbo to [myDomain\Groupname]
go

The user I'm trying to connect with (s_accounting) is member of a domain group, which is granted access to the database using the query above.

Any ideas or suggestions? Do I have to specifically give permissions to each table as well?

Arvo · Accepted Answer · 2011-06-01 11:12:51Z

3

You're mixing two authentication methods - you created/enabled AD (domain) user, but you're using SQL authentication to access server.

Either you need to access server with current domain user credentials (so called integrated security; cannot present correct syntax atm) OR you need to enable SQL authentication on SQL server (if not enabled already) and create user "accounting" (and other needed ones) with specific passowrd.

answered Jun 1, 2011 at 11:12

Arvo

10.6k1 gold badge34 silver badges36 bronze badges

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

2 Comments

Kenny Bones Over a year ago

Aha, so SQL Authentication is the username and password I use via the vba macro code then huh? So if I use trusted_connection=yes in the connection string, this uses the current logged on user instead right? This is a database that several people are supposed to connect to. So, this means that I should use integrated security and make sure each user is member of the group that's given access to the database then?

Arvo Over a year ago

Seems correct. Note that I've no direct experience with connection strings, thereby this all is only hinting :)

Collectives™ on Stack Overflow

Excel VBA - Connection to SQL database fails

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related