Solved

Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server Connection

Posted on 2006-06-19
13
2,205 Views
Last Modified: 2008-02-26
I am using Windows XP Professional and have installed MSDE 2000. I have searched the information on the Experts Exchange site and cannot find a solution as it applies to Windows XP. The connection object is set as follows:

Dim objConn
Set objConn = Server.CreateObject("ADODB.Connection")

objConn.Open "Provider=SQLOLEDB;Persist Security Info=False;" & _
                     "User ID=sa; Initial Catalog=Movie;" & _
                     "Initial File Name=C:\MSSQL7\Data\Movie2000.mdf"

In prior discussions on Experts Exchange, there is mention of Windows security settings and security settings for the SQL Server. I don't know how to access those to make changes.
0
Comment
Question by:jbotts
  • 5
  • 4
  • 2
  • +2
13 Comments
 
LVL 29

Expert Comment

by:QPR
ID: 16939302
Hi jbotts,
have a read here
http://support.microsoft.com/kb/889615/en-us
0
 

Author Comment

by:jbotts
ID: 16939367
Thanks QPR
I have read the suggested material and there is a suggestion to go into "Enterprise Manager" which is either not part of Windows XP Professional or I don't know how to access it. The only other solution is to make changes in the registry through the registry editor. I don't know the command line for to run the registry editor.
Thanks,
jbotts
0
 
LVL 29

Expert Comment

by:QPR
ID: 16939409
Ahhh do you have a SQL Server disc?
What you need is "client tools for SQL server" installed on your workstation.
This will allow you to admin the MSDE installation.... create users, make tables etc.

Some development programs have the ability to connect to SQL/MSDE and show you the contents.
WebMatrix is an (free from www.asp.net) program that springs to mind.
0
 

Expert Comment

by:bshah4u
ID: 16939435
Are you trying with MSSQL 7 ot MSSQL 2000?? as your File path suggests MSSQL 7??
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 16940075
should include pwd parameter in your connection string.

objConn.Open "Provider=SQLOLEDB;Persist Security Info=False;" & _
                     "User ID=sa; Pwd= sapassword Initial Catalog=Movie;" & _
                     "Initial File Name=C:\MSSQL7\Data\Movie2000.mdf"
0
 
LVL 8

Assisted Solution

by:wraith821
wraith821 earned 50 total points
ID: 16941421
MSDE installs with only windows authentication enabled. Not Mixed SQL and NT Authentication.

First you have to edit the registry and enable Mixed Authentication
1. Stop the MSSQLSERVER Service
2. Edit the key or confirm that its value is 2 [HKLM\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer - LoginMode = 2]
3. Restart the MSSQLSERVER Service.
4. With vb or vbscript. Make a trusted connection. Then run the following Stored Procedure

sp_password null, 'password', 'sa'

once Mixed Authentication is enable you may be able to connect with sa and no password (bypass step 4). you'll just have to test that. I've always set mine.

0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:jbotts
ID: 16947654
I have done the following:

1. Changed the registry to enable Mixed Authentication
       HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer - LoginMode = 2
2. Code for the connection has changed to:
       objConn.Open "Provider=SQLOLEDB;Persist Security Info=False;" & _
                            "User ID=sa; Pwd= sapassword Initial Catalog=Movie;" & _
                            "Initial File Name=C:\MSSQL7\Data\Movie2000.mdf"

The connection still does not work. The message is:
       Login failed for user 'sa'.
0
 
LVL 29

Expert Comment

by:QPR
ID: 16947666
and the password you are using in your conn string is definately the SA's password (case sensitive)?
0
 

Author Comment

by:jbotts
ID: 16947757
I am new at this. I just installed the MSSQL Server (MSDE 2000). I am not sure what the user name or passwords are. How may I find out?
0
 
LVL 29

Expert Comment

by:QPR
ID: 16947769
SA username is always SA.
MS SQL propmts you for a SA password when you install SP3 or higher (if none exists).
I'm not sure about MSDE

Try not passing a password at all e.g. User ID=sa; Pwd=
Also, should there be a semi colon (;) betwen the logon credentials and the initial catalog?
0
 

Author Comment

by:jbotts
ID: 16947854
QPR,
I made the corrections in the code and got the same message.
0
 
LVL 29

Accepted Solution

by:
QPR earned 75 total points
ID: 16947882
Try setting/reetting the SA password
http://support.microsoft.com/?kbid=322336

Then you can use whatever you set it to which will either fix your fault or elimanate that from the possible reasons.
0
 
LVL 8

Expert Comment

by:wraith821
ID: 16950474
typical... i get a B and an assist because i told you exaclty what the link the accepted answer gave you? again the world makes since
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now