CODEWITHSUNDEEP
sqlvbaexcel
Kenny Bones
Kenny Bones

Reputation: 5129

Excel VBA - Connection to SQL database fails

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?

Upvotes: 0

Views: 4260

Answers (1)

Arvo
Arvo

Reputation: 10570

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.

Upvotes: 3

Related Questions