?
Solved

Connection string for SQL authentication

Posted on 2006-05-15
7
Medium Priority
?
352 Views
Last Modified: 2011-09-20
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.
0
Comment
Question by:JOSHUABT
  • 4
  • 2
7 Comments
 
LVL 39

Expert Comment

by:appari
ID: 16687160
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"
0
 
LVL 39

Expert Comment

by:appari
ID: 16687166

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

Expert Comment

by:HuyBD
ID: 16687171
sa user is standard user(build in), You can go to sercurity->Login->sa->properties to change database access property with your DBName
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:JOSHUABT
ID: 16687172
Hello appari,
Adding "Trusted_Connection=no" to the connection string doesn't work either.
0
 
LVL 39

Expert Comment

by:appari
ID: 16687219
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
0
 
LVL 39

Accepted Solution

by:
appari earned 2000 total points
ID: 16687227

to change the mode in management studio connect to your server using windows authentication and right click on your database engine node and select properties. in security section you can see the current mode windows or mixed mode. change it to mixed mode and try again.
0
 

Author Comment

by:JOSHUABT
ID: 16692774
Thanks,
I have changed to mixed mode and I was able to login as sa.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question