Integrated Security vs. User ID/Password

[pmurch]

It seems so often that security is what stumps me. My situation:
1. Running an Excel spreadsheet with VBA code that executes a stored procedure on our SQL server.
2. We have an SQL login called abcdefg that has been given Execute rights to the stored procedure.
3. The connection string for the recordset specifies "User ID=abcdefg;Password=tuvwxyz;".
4. When I run the VBA macro, it fails with an error "System Error &H80040E21". When I remove the "User ID=..." from the connection string and replace it with "Integrated Security=SSPI;", it works successfully.

While it's nice that it works, it's not acceptable in our environment because using Integrated Security means each user has to be given rights on the SQL server. And besides, I've always thought one reason for using the "User ID/Password" construct is that the calling code passes the specified credentials, and the person's credentials are not involved.

Am I missing something obvious here? Thanks in advance for any help you can pass along.

 

[MakeItSo]

Have you tried explicitely setting "TrustedConnection=False" in your connection string?

“Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family.” (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

 

[bborissov]

Check what it the Server authentication. It should be Mixed mode if you want to use SQL user, instead of Windows one.

Borislav Borissov
VFP9 SP2, SQL Server

 

[Olaf Doschke]

Did it work before?

Googling the error it has to do with Multiple-step operations failing, not with the Connection.

What line of the VBA code Fails? Is it really already the Connection? Or does executing some query cause it?

You have to have mixed mode for a single SQL Server user to work, and abcdefg then won't be a Windows user, but an integrated security user.

>I've always thought one reason for using the "User ID/Password" construct is that the calling code passes the specified credentials, and the person's credentials are not involved.
That's right, but that only works in either mixed mode or only SQL Server Authentication mode. IIRC, SQL Server Authentication alone isn't available anymore.

Anyway, you can also put all users needing Access into a Group and configure permissions to that Group rather than each individual user. You do have the work to define sucha Group, but can also restrict file Access and other stuff with such application user groups.

Bye, Olaf.

 

[pmurch]

Thank you all very much for your suggestions. MakeItSo -- I tried setting Trusted Connection to False, but still received the same error. Borislav -- I verified that we're executing SQL Server in Mixed Mode. Olaf -- it fails on the Recordset.Open which follows directly after the Connect.Open. It would surprise me less if it failed on opening the connection object's Connect.Open (where the connection string is specified), but it gets past that successfully and chokes on the Recordset.Open (which uses that connection object). It does not seem to be execution of the query (which is simply a stored procedure with no parameters), since it completes successfully with my personal ID (which has admin rights).

If anything strikes you as a possibility, be sure I appreciate any help you can give!

 

[MakeItSo]

1.) Set a breakpoint at the recordset query line and check the status of your connection after "Open()". Is its ConnectionState really open?
2.) Check that the specified user also has at least read rights one the database.
3.) Schema issue? It might even be enough instead of querying "SELECT xyz FROM [mytable]" to use "SELECT xyz FROM [dbo].[mytable]". It's best practice anyway.

Good luck,
MakeItSo

“Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family.” (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

 

[bborissov]

You should check if the user you use to connect has enough rights.

Borislav Borissov
VFP9 SP2, SQL Server

 

[Olaf Doschke]

>abcdefg that has been given Execute rights to the stored procedure.
But does abcdefg also has rights on the tables involved in the stored procedure?

Bye, Olaf.

 

[gmmastros]

Olaf,

If you have execute rights on a stored procedure, then you don't need any access to the underlying tables. This is one of the many benefits of using stored procedures.

pmurch,

I agree with Boris. This sounds like a permission issue. As a test, can you write some code that establishes the connection and then execute this query "Select 1 As TestValue". If successful (meaning you get a recordset with 1 row and 1 column), then you probably have a permissions problem.

Also... can you connect to the database using SQL Server Management Studio, using the SQL Authentication for user abcdefgh? If so, can you use the database? If so, can you execute the stored procedure? In my experience, you get better error messages through SQL Server Management Studio.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom