SQL Server 2005 "Login failed for user" sa

Question

I have not been able to install SQL Express SP2 (standard or advanced) on Vista Ultimate (RTM). Every time I try to install, I get the following error message:

SQL Server Setup could not connect to the database service for server configuration. The error was: [Microsoft][SQL Native Client][SQL Server]Login failed for user 'sa'. Refer to server error logs and setup logs for more information.

When I check the logs, the only information I can find that is at all useful is:

Doing Action: Do_sqlScript
PerfTime Start: Do_sqlScript : Thu Mar 01 21:35:42 2007
Service MSSQL$SQLEXPRESS with parameters '-m SqlSetup -Q -qSQL_Latin1_General_CP1_CI_AS -T4022 -T3659 -T3610 -T4010' is being started at Thu Mar 01 21:35:42 2007
SQL service MSSQL$SQLEXPRESS started successfully waiting for SQL service to accept client connections
Service MSSQL$SQLEXPRESS started at Thu Mar 01 21:35:48 2007
SQL_ERROR (-1) in OdbcConnection::connect
sqlstate=28000, level=-1, state=-1, native_error=18456, msg=[Microsoft][SQL Native Client][SQL Server]Login failed for user 'sa'.

        Error Code: 0x80074818 (18456)
Windows Error Text:   Source File Name: lib\odbc_connection.cpp
Compiler Timestamp: Wed Jun 14 16:28:15 2006
     Function Name: OdbcConnection::connect@connect
Source Line Number: 148

Has anyone else run into this issue and resolved it? I have seen posts for earlier versions of SQL Express that said to run the configuration manager and change the account types to Local System rather than Network Service, but I don't have the configuration manager installed. If I set the accounts to use Local System by changing the login info during setup, I get the same error.

 

Thanks,

 

Mark

 

Answer 1

Is the sa user enabled ?

Jens K. Suessmeyer.

---
http://www.sqlserver2005.de
---

Answer 2

I did not disable it. How can I check this?

Thanks,

Mark

Answer 3

hi Mark,

open SQL Server Management Studio Express and log in as a local administrator with a trusted connection...

navigate to the Security->Logins node of the Object Browser and select the "sa" login... rx click->Properties.... in the "Status" tab verify the "Login: Enabled" property has been set..

regards

Answer 4

Hello,

I installed SQL Server Management Studio Express, but there is nothing to log in to. If I select "Compact Edition", it will create a new compact database, but if I select "Database Engine", the Server Name drop down is empty. I tried to connect to local host using Windows Authentication (I'm running as an Admin) and I receive an error:

TITLE: Connect to Server
------------------------------

Cannot connect to localhost.

------------------------------
ADDITIONAL INFORMATION:

An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)

I don't think the install for SQL Express ever got far enough to actually install the services for SQL Server. The install fails and rolls back right when it tries to start the service.

Thanks,

Mark

Answer 5

Hi Mark,

Is this a new install or are you upgrading from an earlier version? Also, what authentication mode are you selecting?

If your installation was not successfull, they you won't be able to log in using SSMSE, that's a red herring.

Answer 6

Mike,

This was a new install, but I did have a version of MSDE installed previously for Sony Vegas. I had uninstalled the MSDE version prior to starting the install of Express.

I have also completely uninstalled all SQL components via control panel and deleted the Microsoft SQLServer directory from Program Files before initiating the install and run Registry Mechanic to clean up invalid registry entries.

It appears that the install is actually starting the service but is failing trying to configure the service. If I go into services at the point that setup is failing and rolling back, the SQLExpress service is running and I can start the SQL Browser service, but I can't connect. I keep getting a connection timeout error.

Oddly enough, at the point of installation where it fails, I have no copy of the SQL Express Manager or Configuration Manager available either from the Start Menu or in the Program Files directory. I had installed the SQL Express Manager separately based on a previous response.

The install tells me that it can't log in as SA. Short of wiping my machine and starting from scratch, I'm guessing that there is something in the registry that is preventing the install, but I have no clue as to what that might be.

I've been looking for a way to install the Manager and SQL Native Client so they are not disabled/deleted during the install. I'm also trying to figure out if I can reset options from the command line once the service is running.

Any tips would be greatly appreciated.

Thanks,

Mark

Answer 7

Sorry, I forgot.

I have tried all of the authentication modes.

Mark

Answer 8

Also,

During the install, when the SQL Express service is running, if I attempt to create a new ODBC Native SQL connection, the error message is as follows:

Connection failed:
SQLState: '08001'
SQL Server Error: 2
[Microsoft][SQL Native Client]Named Pipes Provider: Could not open a connection to SQL Server [2].
Connection failed:
SQLState: 'HYT00'
SQL Server Error: 0
[Microsoft][SQL Native Client]Login timeout expired
Connection failed:
SQLState: '08001'
SQL Server Error: 2
[Microsoft][SQL Native Client]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.

Thanks for your help,

Mark

Answer 9

Mike,

I also have installed VS Team Suite, but I did not install SQL as part of the install. When I went back and tried to install, I get the same error message. Additionally, I am able to reinstall the MSDE edition from the Sony Vegas product.

Thanks again,

Mark

Answer 10

Hi

The solution for when I ran into this problem was to deactivate the firewall, Vistas own. I had it set to block everything on all levels and it did this silently. After I allowed the outbound traffic the istallation worked like a charm.

Hope this helps.

/Jonas

Answer 11

Hello,

Unfortunately, this did not work for me. I don't know what could be blocking the install. I'm going to uninstall the components that did install (like the Native Client) and see if I can clean out anything in the registry related to SQL and try again with the firewall off.

Thanks,

Mark

Answer 12

I'm going to move this over to the setup forum to see if they have any better ideas than me.

Mike

Answer 13

You might want to reference the following article to see if it fixes your problem.

http://support.microsoft.com/default.aspx/kb/919390/

 

Michelle

Answer 14

Hi,

I get the same problem, but there doesn't seem to be a way out of it.

1. sa account is disabled, so I can't log in as sa

2. If I log in using windows authentication (the only way available to me), I don't have permissions to activate the sa account, or to do anything else - I need to log in as sa before I can update my account!

Catch 22!

Answer 15

I am a university professor teaching database design and implementation at a major university and have had two students running into similar problems as reported above.  We started the current term with Windows XP but both went out and bought new laptops over spring break (instead of partying in Vegas like I did) and neither is able to operate SQL Server 2005 Developers' Edition under Vista.  In both cases the install, including SP 2 proceeds normally and Management Studio is available but when they attempt to connect to the "(local)" or "." database they receive a login failed for user ___ error.  Because of the need for disconnected group development I have all students install and operate using Windows Authentication at all levels.

I am wondering if the way Vista communicates the current logged in user to SQL Server is different from the way XP did it and if that is the source of the problem.  I noted that one poster indicated that they could connect with Windows Authentication but we aren't able to get that far. 

Any additional assistance would be greatly appreciated.

Answer 16

Hi Mark

i am also getting this error - did you find a solution?

if so i would really appreciate it if you would email me on [e-mail]

 

thanks

Steve

 

Answer 17

Hi,

 

The solution to my problem was to re-install SQL Server Express.

 

There is a step during installation where you can select the mode of authentication for SQL Server. The choices are:

 

1. Windows Authentication - this is what I first selected, but reading around the internet, the SA account is disabled by default in this mode. If there is no SA account, you won't be able to login as an administrator and as such can't add or change other users (including yourself).

2. SQL Server - You will be prompted for a SA password, and SA will be enabled.

3. Mixed Authentication - A combination of 1 and 2. SA is enabled in this mode (this is what I use on my PC as it makes logging in quick, and I only use the database for development).

 

There may be some way of changing the authentication mode from 1 (above) to 2 or 3, but I found re-installing the quickest option.

Answer 18

because please read the manual of sqlserver-2005 ,this fully on server os only other than install on  sql native client.and only work windows authentication .

Answer 19

I tried all of the above options with no success. The install never actually completes so I am reinstalling every time I try to install.

 

I just did a fresh install of Vista Ultimate and will try to install again, but since my first attempt was on a clean Vista install, I'm not holding my breath.

 

Thanks,

 

Mark

Answer 20

my solution was to disable the mcafee personal firewall - install was then successful - then re-enabled mcafee

 

Answer 21

I was having a similar problem, and the solution for me was to disable the Vista User Account Control feature.

Answer 22

If you disabled the sa (or changed to native Windows authentication) you will not be able to access the Server anymore. (point) If you want to "rescue" your databases, you will have to deattach the databases (shutdown the services and copy them somewhere) , save them somewhere, install SQL Server from scratch and attach the databases again.

Jens K. Suessmeyer.

---
http://www.sqlserver2005.de
---

Answer 23

   hey there mark i could not help but see your distress in using Sony Vegas, I just updated my computer and thougth I'd share this finding with you.

first of all uninstall sony manger by itself, then download from from microsoft SQL SEVER EXPRESS ADVANCED with service pack2, you dont need to download .netframe 2.0 because Vista already ships with it.

 

next stop, load SQL express 2005 advanced, while loadin read the next screen when you select next with the mouse, make sure u enable both windows and SQL authentication, on the next screen enable sa, by default it will enable the grant permission, and enble. no need to worry. Next is to click on SQL SEVER EXPRESS manager and navigate to security, open all tabs until you see named pipes, click on local only, click ok then go and reload sony media manager which you can find in the programs/sony setup/mdeiamgr (folder) just click the setup tab from this folder and it will reinstall the media manger.

 

Media manger will reconfigure itself to run on SQL Server EXPress, when the windows popup window says, check online for solution DONT Click it, Choose run program instead.

 

if you have installed the programs and they are not working. before you start the above mention go into your control panel and select restore to an earlier time, it will erase all instances of the troubled program, then start the above.

Answer 24

Hey catch 22 there is a way, uninstall or restore computer to an earlier time and download SQL SERVER ADVANCED  WITH SP2 for vista, when load loading make sure to enable both SQL and WINdows AUTHENTICATION.

 

Answer 25

EASY Professor

 

Just restore computer to an earlier time to remove all instaces of the previous, download the SQL SERVER EXPRESS ADVANCED WITH SP2 FROM Microsoft.

upon loading enable both SQL and WINDows AUthentication, make sure to say yes or click on SA authentication when loading and after loading go to the SQL manager navigate to securities check the tabs unlti u find named pipes, uncheck it by clicking on local only. peace out

Answer 26

Hey check my post you'll find the solution

 

Answer 27

check my post under nattygreg you'll find a solution there read everything

Answer 28

Your issue is related to Vista's UAC...

 

1. Right click the short-cut that you use to launch Management Studio

2. Select "Run as administrator" from the context menu

3. Login to SQL Express instance using Windows Authentication

4. Enable sa account

Answer 29

Use the Surface Area Configuration tool to add a computer administrator as as SQL administrator. This is not at all obvious and I feel for you. I spent 4 hours trying to do this.

 

Answer 30

Did you have any luck resolving this issue?

 

I receive the same error, the service is running at the end but I cannot connect with sa or windows authentication.

Even when I try to install without mixed auth, only windows, same error. sa account cannot connect to database service....

 

How did you get on with the forums....any luck....please share the knowledge....

 

Leo

Answer 31

i don't know if your scenario is the same as mine but i had bought a new dell laptop with windows vista basic & mcafee protection pre-installled - when i de-activated mcafee the installation worked perfectly & then i re-activated it again

 

 

 

Answer 32

hey guys im new here and i run the same problem as yours what we did was we created a new login and use windows authentication since it is being asked everytime i connect to the SQL Server 2005..

by doing this

a. login using administrator > then Click Security > Login > right click > new user

b. new user > click search button then find the user in the domain... (i am doint this temporarily since i really need to run my program.

c. Under server rule click sysadmin

d. under user mapping tick the database that you need

e. make sure that it is grant and enabled under status

 once you have chosen the user then click ok

then try running it...

 

good luck

 

by the way im using visual basic 6 and MS SQL 2005.. and is used DSN as my connection...

 

:-)

 

Answer 33

loopyloo wrote:

my solution was to disable the mcafee personal firewall - install was then successful - then re-enabled mcafee

 

please tell me procedure to deativate and reactivate the mcafee as it ended up in a fruitful solution for u as system is replica of urs...

Answer 34

Hi Guys,

try running Sql management studio under the administrator account (ie: right click Sql Server 2005 Management Studio -> Run as Administrator)

if your problems are due to AD login issues etc, this could help out.

Answer 35

Hi

To enable 'sa' account, run the SQL Server as an administrator by right click on it and select 'Run As' from the content menu.

Vista sucks in this case due to stringent security policies. Another way is Start->Programs->Microsoft SQL Server 2005->Configuration Tools-> SQL server suface area configuration.

In this window, just below the help link which says 'Read more about configuring the SQL server access area', you will get a link 'Add Administrator'. But mind you, this is not the case always. You may see or may not see this link. It's mysterious. If it's not visible for you then you have to restore to first option that is to 'Run As' administrator.

HTH,
-Hardik

Answer 36

mspaeth wrote:

I have not been able to install SQL Express SP2 (standard or advanced) on Vista Ultimate (RTM). Every time I try to install, I get the following error message: SQL Server Setup could not connect to the database service for server configuration. The error was: [Microsoft][SQL Native Client][SQL Server]Login failed for user 'sa'. Refer to server error logs and setup logs for more information. When I check the logs, the only information I can find that is at all useful is: Doing Action: Do_sqlScript PerfTime Start: Do_sqlScript : Thu Mar 01 21:35:42 2007 Service MSSQL$SQLEXPRESS with parameters '-m SqlSetup -Q -qSQL_Latin1_General_CP1_CI_AS -T4022 -T3659 -T3610 -T4010' is being started at Thu Mar 01 21:35:42 2007 SQL service MSSQL$SQLEXPRESS started successfully waiting for SQL service to accept client connections Service MSSQL$SQLEXPRESS started at Thu Mar 01 21:35:48 2007 SQL_ERROR (-1) in OdbcConnection::connect sqlstate=28000, level=-1, state=-1, native_error=18456, msg=[Microsoft][SQL Native Client][SQL Server]Login failed for user 'sa'.         Error Code: 0x80074818 (18456) Windows Error Text:   Source File Name: lib\odbc_connection.cpp Compiler Timestamp: Wed Jun 14 16:28:15 2006      Function Name: OdbcConnection::connect@connect Source Line Number: 148 Has anyone else run into this issue and resolved it? I have seen posts for earlier versions of SQL Express that said to run the configuration manager and change the account types to Local System rather than Network Service, but I don't have the configuration manager installed. If I set the accounts to use Local System by changing the login info during setup, I get the same error.   Thanks,   Mark

 

I am experiencing the same issues when trying to install SQL Express SP2 Advanced on Vista Ultimate. I have tried everything.

 

SQL Server Express WILL NOT complete the installation process.

 

It is a clean install. I have never had SQL Express installed on this machine before. I have disable UAC, anti-virus, and firewall. No luck so far.

 

Anybody have a solution?

 

Thanks!

Answer 37

Hey guys, I am surprised that so many people are suffering with this error when it is so easy to fix. Error Code: 18456 means that your SQL Server is configured for windows authentication only. Possibly, during the initial install you did not check windows + sql authentication. To fix this follow the instructions on this page. http://technet.microsoft.com/en-us/library/ms188670.aspx

Answer 38

Sorry ROman, but that is actually not true. 18456 can have different reasons to appear, using SQL Server authentication when Windows authentication is allowed only is only one of them:

 

http://blogs.msdn.com/sql_protocols/archive/2006/02/21/536201.aspx

 

-Jens

Answer 39

Did anyone get the fix?

 

I too have the same problem. I was successfully able to install in 2 servers however in the 3rd server the error "login failed for sa" just did not go...

There has to be some solution, even after choosing mixed mode, i still get the same error!!What password is sa getting validated aganist?

Answer 40

Thanks for your input Roman...your link did the job for me!! 

R

Answer 41

Hi
 Pls refer this

http://aspdotnetexpert.blogspot.com/2008/06/login-failed-for-user-sa-reason-not.html

Answer 42

Thanks R, you save me.