Calling SQL Server stored procedures 1

[Katy44]

I hope this is the right forum, sorry if it isn't.

I have an Access (2000) form that calls SQL Server (2000) stored procedures. I pass in the connection string:

sqlConn.Open "DATA SOURCE =OurServer; USER=sa PASSWORD=pwd; INITIAL CATALOG=DatabaseName"

and this works fine

When I try to use integrated security instead:

sqlConn.Open "Data Source=OurServer; Integrated Security=SSPI; Initial Catalog=DatabaseName"

it fails on that line and throws an error:

Run time error '-2147217887 (80040e21)'
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done

Is this something to do with trying to use integrated security through Access? Specifically SSPI? I haven't managed to find a lot about it, so any help would be appreciated.

 

[Katy44]

I needed to include Microsoft ADO Extension for DLL and Security and "Imports ADOX".

I'm now getting another error, but I think this problem at least has been solved.

 

[cmmrfrds]

I find it is easiest to test the connection string using the UDL Wizard.

How to invoke the wizard.
1. use notepad and save an empty file as text on your desktop.
2. change the extention on the txt file to udl. The icon will change to the wizard.
3. walk through the tabs to build the connection.
4. test the connection.
5. open the udl file with notepad and you will see the connection string which can be pasted into your app.

 

[nice95gle]

Katy,

It has to do with your security setting, I had the same problem.

Try this link it helped me out

http://support.microsoft.com/default.aspx?scid=kb;en-us;269495

 

[Katy44]

cmmrfrds, I have had the same problem again - I searched for the error messgae and found my old post! I tried what you suggested and it worked wonderfully. Thanks very much, and I'm sure I'll be using it again in the future!!

 

[cmmrfrds]

I am glad the suggestion was helpful.

 

[datras]

Hi cmmrfrds
Yes, it was helpfull!!!