Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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?

share|improve this question

1 Answer

up vote 3 down vote accepted

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.

share|improve this answer
1  
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? – Kenny Bones Jun 1 '11 at 11:54
Seems correct. Note that I've no direct experience with connection strings, thereby this all is only hinting :) – Arvo Jun 1 '11 at 13:53

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.