asked on

Connection string for SQL authentication

Hello there,
I have been using the following connection string in my VB6 code to connect to SQL2005 DB:
ConnStr = "Provider=SQLNCLI;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=" & DBName
& ";Data Source=" & ServerName
I understand I am using Windows authentication in the string and it is working fine.

Now, I want to try SQL authentication.
So I change my connection string as following:
ConnStr = "Provider=SQLNCLI;UID=sa;PWD=sapassword;Persist Security Info=False;Initial Catalog=" & DBName
& ";Data Source=" & ServerName

It fails with the following error message:
"Login failed for user 'sa'. The user is not associated with a trusted SQL connection."

I guess I have to configure user 'sa' further, but don't know how.

Thanks for your help.

appari

try adding "Trusted_Connection=no"

change your connection string to

ConnStr = "Provider=SQLNCLI;UID=sa;PWD=sapassword;Persist Security Info=False;Initial Catalog=" & DBName
& ";Data Source=" & ServerName & ";Trusted_Connection=no"

appari

for a complete reference of connection strings http://www.connectionstrings.com/

HuyBD

sa user is standard user(build in), You can go to sercurity->Login->sa->properties to change database access property with your DBName

JOSHUABT

ASKER

Hello appari,
Adding "Trusted_Connection=no" to the connection string doesn't work either.

appari

are you able to logon using sa from management studio?

if not may be your server is installed to use windows authentication only.
http://support.microsoft.com/kb/555332/en-us
http://support.microsoft.com/kb/889615/en-us

ASKER CERTIFIED SOLUTION

appari

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

JOSHUABT

ASKER

Thanks,
I have changed to mixed mode and I was able to login as sa.