Solved

Login failed for user 'sa'

Posted on 2004-09-26
10
1,183 Views
Last Modified: 2008-02-01
I am following a example in a book.  It is dealing with ASP, which covers this area well, but gives very little explanatin on installing sql 2000

I am using msde version, and installed it, it did not ask for a user, and so I am using the default - where do I go to check the user

with the asp snippet as such:
objConn.Open "Provider=SQLOLEDB;Persist Security Info=False;" & _
               "User ID=sa;Initial Catalog=Movie;" & _
               "Initial File Name=C:\MSSQL7\Database\Movie2000.mdf"

Error Type:
Microsoft OLE DB Provider for SQL Server (0x80004005)
Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection.

i really do not know where to go to fix this
THANks
0
Comment
Question by:sahrom
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 12155808
Use this connection string:
objConn.Open "Provider=SQLOLEDB.1;Password="";User ID=sa;Initial Catalog=Movie;Data Source=YourServerNameGoesHere"
0
 

Author Comment

by:sahrom
ID: 12155878
thank you very much for your reply

I now get the following error

Error Type:
ADODB.Connection (0x800A0BB9)
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
/BegAsp/Connect.asp, line 29

-this is more of the code - or how i have it now:

Dim strDatabaseType
'Choose one of the following two lines, and comment out the other
'strDatabaseType = "Access"
strDatabaseType = "MSDE"


If strDatabaseType = "Access" Then
  objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
               "Data Source=C:\datastores\Movie2000.mdb;" & _
               "Persist Security Info=False"
Else
   objConn.Open "Provider=SQLOLEDB.1;Password="";User ID=sa;Initial Catalog=Movie;Data Source=CPQ12126180872"
End If
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 100 total points
ID: 12155937
Take a look at this link:
http://www.able-consulting.com/MDAC/ADO/Connection/OLEDB_Providers.htm#OLEDBProviderForSQLServer
and the example given:
oConn.Open "Provider=sqloledb;" & _
           "Data Source=myServerName;" & _
           "Initial Catalog=myDatabaseName;" & _
           "User Id=myUsername;" & _
           "Password=myPassword"

The only difference between that and the one I suggested is the provider (sqloledb vs sqloledb.1).
0
 
LVL 6

Assisted Solution

by:RaisinJ
RaisinJ earned 100 total points
ID: 12157086
Here is the site I always use for connection strings as I am always forgetting them.  This site has connection strings for all DBs and all security types.

http://www.connectionstrings.com/

Also, if you have a default install of SQL Server, the password for sa is blank, in your connection strings, "Password=;",  you should not have to put double or single quotes.  
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 12157155
RaisinJ makes a good point:
>>Also, if you have a default install of SQL Server, the password for sa is blank, in your connection strings, "Password=;",  you should not have to put double or single quotes.  <<

Anotherwhat you can express an empty password is as follows:
objConn.Open "Provider=SQLOLEDB.1;User ID=sa;Initial Catalog=Movie;Data Source=CPQ12126180872"
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 5

Expert Comment

by:amit1978
ID: 12157726
Hello  sahrom,

This is is a problem of authentication mode selected by default MSDE install as windows authentication mode. In ISQL it login without any user name and password.

Bcoz it is installed as windows autentication mode it does not give permission of login as SA and flesh back an error not trusted user.

U have to pass some paramiter to change the authentication mode while executing seup.exe of MSDE.

Hope this will help

Amit Jain
0
 
LVL 5

Assisted Solution

by:amit1978
amit1978 earned 300 total points
ID: 12157771
By MSDN K. Base
~~~~~~~~~~~~~


SUMMARY
When using SQL Server 2000 Desktop Engine Windows Installer Merge Modules, an option named SECURITYMODE can be used to change the default login authentication mode from Windows NT authentication to SQL authentication.
MORE INFORMATION
The authentication mode can also be changed at the time of the installation by using the following command-line setup parameter for SQL Server 2000 Desktop Engine: SECURITYMODE=SQL
                        
For more information, refer to the "3.1.4 Desktop Engine Setup Parameters" section in the Readme.txt file for SQL Server 2000 Desktop Engine installation.

By default, SQL Server 2000 installation sets the login authentication to Windows authentication mode. This can be easily changed after the install by connecting to the server by using any installation of SQL Server Enterprise Manager (SEM). For more information regarding changing authentication mode by using SEM, refer to the "Authentication" topic in SQL Server Books On-Line.

Another way to change the security mode after installation is to stop SQL Server and set the appropriate registry key for your installation:

Default instance:
HKLM\Software\Microsoft\MSSqlserver\MSSqlServer\LoginMode

Named instance:
HKLM\Software\Microsoft\Microsoft SQL Server\Instance Name\MSSQLServer\LoginMode

to 0 or 2 for mixed-mode or 1 for integrated. (Integrated is the default setup for the SQL Server 2000 Data Engine.)

Note The 0 and 2 values have the same functionality. They both set the security to mixed-mode.

WARNING: If you use Registry Editor incorrectly, you may cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that you can solve problems that result from using Registry Editor incorrectly. Use Registry Editor at your own risk.

To set this key to mixed-mode, follow these steps:
Stop SQL Server and all related services, such as SQLAgent, from the Services control panel.
Click Start, click Run, type regedt32, and then click OK.
Find the HKEY_LOCAL_MACHINE window on the local computer.
Navigate to the following registry key for the default instance:
HKEY_LOCAL_MACHINE\Software\Microsoft\MSSqlserver\MSSqlServer

Navigate to the following registry key for a named instance:
HKLM\Software\Microsoft\Microsoft SQL Server\Instance Name\MSSQLServer\LoginMode

On the right-hand pane, find the value LoginMode and double-click it.
In the DWORD Editor window, set the value to 0 or 2. Also, make sure that the Hex radio button is selected.
Click OK.
Restart SQL Server and SQL Server Agent services for this change to take effect.
0
 
LVL 5

Expert Comment

by:amit1978
ID: 12157779
0
 
LVL 5

Expert Comment

by:amit1978
ID: 12157808
MSDE will accept any password you give it, but whether you need to use quotes or not is a different matter.

Have a look in your MSDE setup.exe directory, there should be a help file (HTML) which talks about how to run the install program (but it is very complicated).

The setup runs for me with just the sa password parameter, but if you want (need) to set the instance name, you here is an example of more parameters: (you want INSTANCENAME)

setup /upgradesp sqlrun INSTANCENAME= InstanceName SECURITYMODE=SQL UPGRADEUSER=AnAdminLogin UPGRADEPWD=AdminPassword DISABLENETWORKPROTOCOLS=1

Amit Jain
0
 

Author Comment

by:sahrom
ID: 12159719
thanks all for the information and links, they are all usefull

so the problem is not how I have the US ID and Passward, but the user
authentication mode
with my options listed by amit1979
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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.

706 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

20 Experts available now in Live!

Get 1:1 Help Now