Understanding "login failed" (Error 18456) error messages in SQL Server 2005

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Alan — Mon, 13 Mar 2006 06:14:58 GMT

Im developing the 5 stars programme in .NET
Im having this problem with sql server 2005 in the first example of c#:

Error: 18456, Severity: 14, State: 16.

What should I do?

My email: alankind@hotmail.com

Thanks!

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Ignacio Abel — Tue, 14 Mar 2006 02:25:56 GMT

I have exactly the same problem using SQL Server 2005.

Cheers,

Ignacio Abel.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Matt Neerincx [MSFT] — Tue, 14 Mar 2006 09:24:19 GMT

State=16 means that the incoming user does not have permissions to log into the target database. So for example say you create a user FOO and set FOO's default database to master, but FOO does not have permissions to log into master.

This can also happen if for example the default database for user FOO is not online (for example the database is marked suspect).

So to check on this theory, try logging the user into some other database and then try using the USE DATABASE command to switch to the target database, you will get a better error message as to the root cause of the failure.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

EH — Tue, 14 Mar 2006 14:45:34 GMT

Hello,
when connecting from ODBC with SQL security, all connections are failing.

Error is 18456, Severity: 14, State: 8.

Creating new logins, and triple-checking the passwords did not help...

Thank you
EH

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Tom — Thu, 16 Mar 2006 03:20:23 GMT

I am having the same error. The database engine will not allow any logons. The other engines seem to be fine. This still means that I do not have permissions for any of the databases. This is using local or domain accounts.

I created this problem by detaching one database running on the server. I went to connect the datafiles and I cannot get into the server at all.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Matt Neerincx [MSFT] — Thu, 16 Mar 2006 04:37:04 GMT

When you say "Same Error" I am assuming you mean:

Error is 18456, Severity: 14, State: 8.

This error is pretty clear, means that the password supplied does not match the password given. Note that passwords in SQL 2005 are case-sensitive, this could be an issue.

If you are a local administrator to the computer, then you should always be able to log into SQL.

Also, try using the admin port if the normal ports are not working, for example, to connect to local default instance:

sqlcmd -E -Sadmin:.

To connect to local named instance:

sqlcmd -E -Sadmin:.\Instance1

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Tom — Fri, 17 Mar 2006 18:17:12 GMT

Thanks so much for your help.

I was able to use SQLCMD to gain access and rebuild access for my database
admin account. I was then able to use this account to reattach the detached
database. When I did this the database and all user access to SQL2005 was
back to normal. I am not sure why this happened. We have about ten other
databases on this SQL server. The same users had access to these tables as
well as the detached table so the detached table was not the only table for
which they had access. The detached table was just a table of 'production'
data.

I will try and see if I can recreate the problem but will wait for
the weekend to try that!

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

gautam — Sat, 25 Mar 2006 11:08:48 GMT

soloution

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Rolf — Mon, 10 Apr 2006 22:51:27 GMT

Re: state 16 - if there is no other database to log into, how do we determine what the problem is? Users have installed SQL Server Express 2005 using Windows Authentication, where user id and password should not be an issue, right?

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

rm — Thu, 20 Apr 2006 23:43:34 GMT

hi there

i'm getting a 'State: 1' (yes, in the server's event log). what could this mean?

thanks in advance!

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

SQL Protocols — Fri, 21 Apr 2006 00:54:41 GMT

Regarding 'State: 1', are you running SQL Server 2005 or SQL Server 2000? Supportability improvements were made to 2005 to make the states more unique but 2000 still reports 'State: 1' in every case.

- Vaughn

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

rm — Fri, 21 Apr 2006 03:46:21 GMT

we're running SQL Server 2005 CTP. server authentication is set to "SQL Server and Windows Authentication mode". when connecting using windows accounts everything is fine, but any attempt to connect as e.g. 'sa' fails with this 'State: 1' error message... greets, rm

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

SQL Protocols — Fri, 21 Apr 2006 19:32:52 GMT

Another question, are you looking at the error message state that the client receives or the error message state in the server error log? By default the server error log is at "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG". Also, you mentioned you're running at SQL2K5 CTP rather than RTM. Which CTP are you using? If you're using an old CTP it may be the case that unique state improvement hadn't yet been made.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

shi — Tue, 23 May 2006 11:53:31 GMT

I'm getting Error: 18456, Severity: 14, State: 27
Could you help me on state 27, please.
I'm running SQL 2005 version 9.00.1399.06
Thx

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Il-Sung Lee [MSFT] — Tue, 23 May 2006 21:03:53 GMT

Hi Shi,

Error state 27 signifies the server could not determine the initial database for the session. Did you specify a database name in your connection string? Are you sure that the name was spelled correctly?

Il-Sung.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Sinish Gopi — Tue, 23 May 2006 21:52:57 GMT

This Error is completly related to SQL Server authontication. This will ome where there will be a mismatch in the Existing Password and the Given passowrd.

I faced this issue when I changed the SQL Server start up account.

sinishkg@hotmail.com

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Nam — Wed, 14 Jun 2006 22:45:38 GMT

I have error state 10 what does that mean?

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

SQL Protocols — Thu, 15 Jun 2006 08:16:34 GMT

Hi, Nam

This forum can help you.

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=92&SiteID=1

Ming.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

EH — Fri, 21 Jul 2006 15:11:16 GMT

Hi,

just a hint for those with state 8 (wrong password):

With SQL 2005, the passwords are CASE-SENSITIVE, see http://support.microsoft.com/kb/907284

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Tan — Tue, 01 Aug 2006 06:48:25 GMT

Hi,

08/01/2006 02:28:39,Logon,Unknown,Error: 18456 Severity: 14 State: 23.

08/01/2006 02:28:47,Logon,Unknown,Error: 18456 Severity: 14 State: 16.

We had the problem with error state 16 and 23 on our SQL 2005 (64 bits). The error came and gone by itself, any ideas?

Tan

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Steve — Tue, 01 Aug 2006 21:56:50 GMT

We get this error trying to connect using tcp/ip. sa can connect using tcp/ip, but all other sql logins fail. This began after creating a TCP ENDPOINT listening on port 1090. The endpoint has been dropped, server restarted. Cannot get sql logins to connect using tcp/ip.

Login failed for user 'PrefAdmin'. [CLIENT: ...]
Error: 18456, Severity: 14, State: 12.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Steve — Tue, 01 Aug 2006 22:27:16 GMT

re: can't connect using tcp/ip - when the logins are in the sysadmin role, they can connection via tcp/ip - otherwise they cannot.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

SQL Protocols — Sun, 06 Aug 2006 19:05:09 GMT

Hi, Steve

The error state 12 indicates that your sql account has no access to the server, have you grant login to it? Can you also try connecting over named pipe, what happens?

Overall, this is not connectivity issue, the following forum can help you w/ the specifiy sql security problem.

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=92&SiteID=1

Good Luck!
ming.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

jeff_yao — Thu, 07 Sep 2006 03:27:05 GMT

I get the following error messages in the sql server error log

Source Logon

Message
Error: 18456, Severity: 14, State: 11.

and

Source Logon

Message
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [CLIENT: 185.23.11.33]

The scenario is: We set up log-shipping (LS) between a clustered sql server system (source server) and a stand-alone sql server box (target server). (SQL Server 2K5 EE + SP1), and LS goes very well, but on the target server, we found the above-mentioned error messages.

Thanks for your help in advance...

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Vineet Dewan — Tue, 12 Sep 2006 14:32:52 GMT

I tried to connect to the SQL Server 2005 Express edition Microsoft Server Management Studio.

I gave the followign:
Authentication: SQL Sever Authentication
Login: sa
Password:

It gave me error 18456
On checking the log, following entry was found
2006-09-12 14:18:19.20 Logon Error: 18456, Severity: 14, State: 7.
2006-09-12 14:18:19.20 Logon Login failed for user 'sa'. [CLIENT: <local machine>]

Please let me know how to allow sa to log in using SQL authentication.

Regards
Vineet Dewan

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Lena Venter — Wed, 13 Sep 2006 16:43:48 GMT

I resolved my issue with the sa login 'state 8' by unticking the enforce password policy in the properties of the sa user, this is for SQL2005 only.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Carmen — Tue, 19 Sep 2006 21:27:54 GMT

Did someone figure out the State: 1 issue?
I am starting my SQL server on sigle mode and trying to login under the account that installed the server but still I get this error:

2006-09-19 13:52:29.29 Logon Error: 18461, Severity: 14, State: 1.
2006-09-19 13:52:29.29 Logon Login failed for user 'ASSUREONB\AssureonManager'. Reason: Server is in single user mode. Only one administrator can connect at this time. [CLIENT: <local machine>]

In
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

SQL Protocols — Wed, 20 Sep 2006 02:45:29 GMT

Hi, Carmen

This is more SQL security area, you can post your question in following forum, many experts can help you shortly:

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=92&SiteID=1

Good Luck!
Ming

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

SD — Tue, 03 Oct 2006 21:55:36 GMT

I have an issue with the report server. We used to have a web farm and not scaled down to a single sql server with the reporting components.

The problem is when I enable windows authetication through IIS it is trying to access the page via "domain\servername$" from client IP x.x.x.x. The server name is previous sql reporting services install. It is no longer installed on there.

I never had reporting services configured until now. How can I have the report server use the domain user credentials rather than "domain\servername$"?

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

marcin — Fri, 13 Oct 2006 07:11:02 GMT

Error: 18456, Severity: 14, State: 5

would anyone know how to correct this error?

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Hans-Georg — Tue, 17 Oct 2006 10:48:10 GMT

Hi @all

We have a SBS2K3 with SQL2k5.

On every Startup we get the following Error:

Ereignistyp: Fehlerüberw.

Ereignisquelle: MSSQL$SHAREPOINT

Ereigniskategorie: (4)

Ereigniskennung: 18456

Datum: 17.10.2006

Zeit: 00:20:25

Benutzer: NT-AUTORITÄT\NETZWERKDIENST

Computer: PDC

Beschreibung:

Fehler bei der Anmeldung für den Benutzer 'NT-AUTORITÄT\NETZWERKDIENST'. [CLIENT: 192.168.2.250]

Weitere Informationen über die Hilfe- und Supportdienste erhalten Sie unter http://go.microsoft.com/fwlink/events.asp.

Daten:

0000: 18 48 00 00 0e 00 00 00 .H......

0008: 0f 00 00 00 50 00 44 00 ....P.D.

0010: 43 00 5c 00 53 00 48 00 C.\.S.H.

0018: 41 00 52 00 45 00 50 00 A.R.E.P.

0020: 4f 00 49 00 4e 00 54 00 O.I.N.T.

0028: 00 00 07 00 00 00 6d 00 ......m.

0030: 61 00 73 00 74 00 65 00 a.s.t.e.

0038: 72 00 00 00 r...

The Client: 192.168.2.250 is the SBS itself with the internal LAN NIC (the SBS have 2 NIC´s for WAN and LAN)

The NT-Authority\Networkservice is in Logon of the DB and have dbcreate and dbsecurity rights.

What can we do?

regards.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Almir Begovic — Thu, 19 Oct 2006 12:44:58 GMT

I have the following error, no severity displayed, I can't ping IP address and it does not belong to us.

Any hel will be appreciated.

Event Type: Failure Audit

Event Source: MSSQLSERVER

Event Category: (4)

Event ID: 18456

Date: 19/10/2006

Time: 09:27:26

User: N/A

Computer: INET

Description:

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

RDuke — Tue, 24 Oct 2006 15:36:39 GMT

I have a VB5 app that's providing the login/password through an ADO connection string, and been unable to establish a successful remote server connection to SqlExpress. Mine keeps going back and forth between state 16 and state 8, and I assure you the password being provided is correct. What gives with this authentication?

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Moshe Rosenberg — Wed, 25 Oct 2006 23:28:57 GMT

We just migrated to a new sql server with SQL Server 2005 installed (we moved from 2000).

We are experiencing two issues that we cannot resolve:

1. The site and database clients that use this SQL Server run very slow now. We have noticed that the lsass.exe process consistantly uses 25% CPU, +/- 10%. When we stop SQL server the lsass.exe process goes down to 0. What can be causing this? The website uses a SQL user account, not a windows user account.

2. We have an error in the event log that might be related to the issue above. It appears every few minutes:

Event Type: Failure Audit

Event Source: MSSQLSERVER

Event Category: (4)

Event ID: 18456

Date: 10/25/2006

Time: 11:54:42 AM

User: NT AUTHORITY\SYSTEM

Computer: MSDB

Description:

The error log from SQL gives more info:

Error: 18456, Severity: 14, State: 16.

What is causing this? How can this be traced?

I am stuck here. All help greatly appreciated.

Moshe

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Nan Tu (MSFT) — Thu, 26 Oct 2006 21:14:48 GMT

Moshe,

One of the improvements in SQL 2005 is that all logins are always encrypted using SSL. If you have frequent connection logins, you will see lsass being quit *active*. Is it your case?

For the error in the ERRORLOG, the STATE tell that the process doesn't have permission of the login database. It apears to use NT authentication, running as localsystem. You can find who is trying to login from your local machine and go from there...

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Mahesh — Tue, 31 Oct 2006 11:42:12 GMT

I keep on getting this error intermittently and the user specified in the error log has got the correct permission.

2006-10-31 08:58:36.01 Logon Error: 18456, Severity: 14, State: 16.

2006-10-31 08:58:36.01 Logon Login failed for user 'AbsLayer'. [CLIENT: <local machine>]

2006-10-31 08:58:37.28 spid53 Starting up database 'AbsLayerRepository'.

I have added retry logic in my code which keeps on retrying the connection for the configurable amount of time, when I get this error I can see in my application log that its retrying the connection and most of the time it passes after 2 - 3 attempts but some time it goes up to 9-10 attempts so I feel it to be unreliable in the live system. Can some one please help me why this error occurs or is there any patch to resolve this issue.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Umair — Sun, 05 Nov 2006 13:34:28 GMT

I have found an Error:

Server Name: UMAIR130786\SQLEXPRESS

Error Number: 18456

Severity: 14

State: 1

Line Number: 65536

It works well using Windows Authentication

but with SQL Server Authentication it results in the above mentioned error.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Juan Peguero — Sat, 11 Nov 2006 00:53:13 GMT

I was getting the same error, and I try everything listed on the Forum, and could not get rid of the error. It looks like there is some funkiness on the ODBC Connection, and it will always use the current user logged on the computer, not the specified SQLSever account, to send over the network for the connection profile.

To eliminate the error, I had to grant the Domain Users access to the SQL Database the program was trying to connect to. In my case, all users had access to the database, but security settings can be put in place to limit the users’ access.

In other words, I could not fixed the problem, so I had to make a workaround.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Bertie — Mon, 27 Nov 2006 02:22:19 GMT

Like every one else, I was frustrated by the strange 18456 error with State=8. I store my password in a textfile, and when I need it, I do a copy and paste into the password field. I use a password something like "Ity@1%6$9#g".

I tried a number of "strangies", but this one worked on more than one occasion: using the direction arrow key pointing left, I moved the cursor back to the beginning of the password. It worked!

Try this at home, folks, it does not hurt a bit, and perhaps it might give the Microsoft boys a hint about the nature of the problem if you report the results.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

KarenM — Mon, 11 Dec 2006 19:58:20 GMT

Il-Sung, thanks so much for writing this up -- definitely the most helpful source of debugging info for login failures to SQL!

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Shawn — Tue, 12 Dec 2006 17:34:18 GMT

What is State 16? I am getting Error: 18456, Severity: 14, State: 16

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

SQL Protocols — Tue, 12 Dec 2006 21:47:31 GMT

Hi Shawn,

State 16 indicates that target database could not be determined. This may mean that the specified database is non-existent, you do not have permissions on the database, or the database may not be online. Also, you can do as Matt mentioned in his comments on March 14 -- try connecting to a different database and then use the USE DATABASE to the problematic database and see what the error is.

Il-Sung.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Cindy — Wed, 13 Dec 2006 23:19:10 GMT

I have an SQL Server 2005 cluster. Windows Authentication works fine on the primary node but after failing over onto the secondary node I am getting the 18456 error State 11.

I noticed someone else posted concerning this error state but I do not see a response to this issue.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

adamfool — Thu, 04 Jan 2007 23:25:34 GMT

I am getting the state 16 error, but it is not recurring. However there is a separate partition on the server that holds the logfiles which I noticed have not changed since the last time I received the state 16 error. Also the partition is almost to capacity. Are these two problems related or do the logfiles not overwrite themselves? I will go ahead and apologize for dumb questions. I have no experience in SQL and my job requires me to administer an enterprise GIS database!

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

prashanth,my mail id is prashanth.krishnan@tcs.com — Fri, 05 Jan 2007 17:48:25 GMT

Hi ,

When I try to start the merge agents in SQL server 2000 I get the error message as below:

________________________________________

The process could not connect to Subscriber 'ATELBSNT65'.

(Source: ATELBSNT65 (Agent); Error number: 20084)

---------------------------------------------------------------------------------------------------------------

(Source: ATELBSNT65 (Data source); Error number: 18456)

_______________________________________

There are 3 servers ATELBSNT65,66,67.

When I try to access merge agents through ATELBSNT67 this error occurs.

ATELBSNT67 is the publisher and ATELBSNT65,66 are the two subscribers.

So please help.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Mash — Tue, 09 Jan 2007 19:36:08 GMT

Hi,

We are running SQl2005 Enterprise CTP2, and we keep getting

Error: 18456, Severity: 14, State: 5.

have already checked and the administrtor is part of the sys admin role

Can any one help please??

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Belsteak — Fri, 12 Jan 2007 11:12:37 GMT

Hello,

We have a brand new server 2005 64 bit SP1. We can't see the logs of the server from the entreprise manager (error).

When i get to the logs by other means, i always see this error :

Logon Error: 18456, Severity: 14, State: 11.

Logon Login failed for user 'NT AUTHORITY\SYSTEM'. [CLIENT: <local machine>]

Do you have any idea ?

Thnks

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Belsteak — Fri, 12 Jan 2007 11:31:38 GMT

Hello again,

I searched a bit more. NT AUTHORITY\SYSTEM login was missing (deleted???) I created a new one, gave the sysadmin role and i can read the logs again...

The why of the disappear of the login is another story, we are DBA and system working on the server...

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Carol Walter — Fri, 12 Jan 2007 21:44:10 GMT

I'm setting up my first SQL2K5. I've set up about seven SQK2K, but all of them use Windows Authentication. This one has to use SQL authentication. This is a research database with a single user. I want to give him the db-owner role. When I try to login with the login I made for him I get an MS SQL Error 4064. Cannot open default database. The database log says Error 18456 Severity 14 State 16. The default database is referenced in the user login window and that database in online. Is there something I need to do besides create a login to the database server for this user, create a login to his default database, and add him to the db_owner role?

Thx.

Error: 18456, Severity: 14, State: 5.

Mash — Mon, 15 Jan 2007 19:38:01 GMT

HI,

A couple of days back i have reported a problem about Error: 18456, Severity: 14, State: 5. and in SQl logs i used to get Login failed for user 'administrator'

The solution was to disable SQl Fibers

The system is running fine now.

Thx

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

khaki — Tue, 23 Jan 2007 14:52:31 GMT

" + title + "

" + title + " — Sun, 28 Jan 2007 23:16:55 GMT

PingBack from http://www.do-ict.nl/2007/01/28/sharepoint-and-sql-server-error-18456/

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

craig hogan — Mon, 29 Jan 2007 14:34:01 GMT

Had same problem state 14, I am running SQL 2005 dev edition with SP1, after some playing around I my user account didn't have access to the MSSQL folder, I found by running the SQL Server Management studio as 'administrator' from the right click menu all works fine.Very odd though as I installed SQL from my user which is an administrator, looks like vista handles user differently.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Tim — Thu, 15 Feb 2007 18:34:25 GMT

Hi, I'm getting a state 1 error with SQL Server 2005 Express - I've followed links from this forum but so far have had no luck at all in finding an answer (or even a cause!)

Help!

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Adam Barnard — Mon, 19 Feb 2007 15:09:41 GMT

Hi,

I have SQL 2000 SP 4 running with both SQL & Windows Auth. selected.

The moment I open Enterprise Manager from a computer that is not on the domain I get the following errors in the NT eventlog although I am using SQL Auth in Ent. Manager:

Logon attempt by: MICROSOFT_AUTHENTICATION_PACKAGE_V1_0

Logon account: [Remote NT User ID]

Source Workstation: [Remote Workstation Name]

Error Code: 0xC0000064

Logon Failure:

Reason: Unknown user name or bad password

User Name: [Remote NT User ID]

Domain: [Remote Workstation Name]

Logon Type: 3

Logon Process: NtLmSsp

Authentication Package: NTLM

Workstation Name: [Remote Workstation Name]

Caller User Name: -

Caller Domain: -

Caller Logon ID: -

Caller Process ID: -

Transited Services: -

Source Network Address: 10.2.5.16

Source Port: 0

I was under the impression that the whole point of SQL Auth. is not to try and Aut. the local logged on user?

Any pointers would be appreciated.

Adam

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Tom — Wed, 21 Feb 2007 18:03:49 GMT

Hi,all.

I got an error state 1.

I created a user called "sa"(because there was no system admin) and I tried to connect to the db engine with this user,but I can't.

"Login failed for user 'sa'. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452)" is displayed.

How can I connect?

please inform me to realhermes618@gmail.com

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Matt Neerincx (MSFT) — Wed, 21 Feb 2007 21:26:14 GMT

You probably don't have mixed mode security enabled on your SQL Server. By default only integrated security is allowed.

Open SQL Server Management Studio and right click on the instance node in the Object Explorer and select Properties. Click on Security page and ensure that "SQL Server and Windows Authentication mode" option is selected. Press OK and restart SQL.

more information about state 11 error

Luc Kremers — Thu, 22 Feb 2007 09:38:11 GMT

Hi,

I have SQL server 2005 enterprise edition and IIS server 6.0 on one machine, and trying to connect through ASP which uses the IUSR_machinename account.

I get error message 'login failed' and the serverlog shows state 11. That is great, but I haven't found any additional information as to solve this. 'server access failure' sounds pretty obscure. Any ideas how to resolve this ?

THanks so much in advance,

Luc

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Nick Pattman — Mon, 05 Mar 2007 21:00:11 GMT

Hi All,

Error: 18456, Severity: 14, State: 16 happened on my server when the owner of the files on the file system is not a user in SQL Server. I had the following scenario:

I imported a database, and specified the name of the imported database in a connection string. This failed to connect with the login failed message, but worked when I connected via master and specified "Use Database" within my query as suggested by Il-Sung.

When trying to generate database diagrams I was then told that the database did not have a valid owner, but when I right clicked on the databse properties an owner (sa) was listed. However, when I looked at the files owner, there was none specified.

To correct this problem in SQL Sever 2005, do the following:

1. Open SQL Server Management Studio

2. Expand Databases

3. Right Click on the Database with problems and choose 'Properties'

4. Left Click the 'Files' node

5. Ensure that an appropriate owner is listed, if none is then set it

That resolved the issue with the database that I had this error with.

Hope this helps,

Nick

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

MCG_Val — Tue, 06 Mar 2007 12:15:01 GMT

Hi All,

We are using the following connection string in VB.NET

"Server=.\PRG;Integrated Security=false;Database=test9 ";User Id=sa;Password=mypassword;"

to connect to a database that we have created previously using a similar connection string:

"Server=.\PRG;database=master;Integrated Security=false;Uid=sa;Pwd=mypassword;"

This works sometimes, but most of the times it returns the following error:

"Cannot open database "test9" requested by the login. The login failed.

we are going crazy!!, can you help us?

Thanks

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Il-Sung Lee — Wed, 14 Mar 2007 02:48:03 GMT

What is the corresponding error in the server's error log?

Thanks,

Il-Sung.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Robert — Wed, 14 Mar 2007 18:17:56 GMT

Error: 18456, Severity: 14, State: 16.

I'm getting this error trying to connect a service to the database and the user I've verified has access to the database that it's trying to connect to. Is there something I need to do differently for a service? The service is related to MOSS 2007. It's the Microsoft Single Signon Service. Any suggestions?

Thanks

Robert

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Jim — Thu, 15 Mar 2007 00:54:27 GMT

I am getting this same error as Error number 18452 Severity 14 State 1 with SQL2005, Windows Authentication ONLY.

My user is permitted on the server, I can connect with Management Studio.

No space in ServerName, Windows firewall disabled.

The calling program is Delphi5

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Ben — Mon, 19 Mar 2007 07:59:56 GMT

I'm seeing error state 23 repeatedly in my logs:

“Error: 18456, Severity: 14, State: 23.”

Despite some scouring of the Web and MSDN I could find no information about this one. Please post the answer if possible.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Matt Neerincx (MSFT) — Mon, 26 Mar 2007 19:15:50 GMT

State 23 is pretty rare. What this means is the server was in the process of shutting down and at the same time some user was trying to log in. SQL blocks new users from logging in when the server is shutting down. So if you have fairly consistent logins I would expect to see this one from time to time when the server is shut down, it's harmless.

"login failed" (Error 18456) error messages in SQL Server no state and no severity shown

Jesus Carranza — Thu, 29 Mar 2007 20:08:15 GMT

Hi Il-Sung,

I'm receiving the Error 18456 in my Microsoft SQL Server 2000 - 8.00.818 Standard Edition but when looking in the ERRORLOG file I cannot see any state not severity number, Do I need to configure anything in order to obtain those?

Regards

Jesus

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Il-Sung — Sat, 31 Mar 2007 00:58:32 GMT

Hi Jesus,

Unfortunately, the correct error state is not reported by the SQL Server 2000 server. There is no configuration that can change this.

Il-Sung.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Sergei — Sun, 01 Apr 2007 16:32:47 GMT

Hi,

My email is sergei@ssw.com.au.

I have got SQL Server 2005 SP2 on WinXp SP2.

I tried sqlcmd -S machine_name -U machine_name\user_name -P user_password.

It failed with error: "Login failed for user machine_name\user_name"

I can open SSMS using run as machine_name\user_name,connect using windows authentication (in this case machine_name\user_name) and it works fine.

My question is why this command-line does not work

sqlcmd -S machine_name -U machine_name\user_name -P user_password?

Sergei.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Matt Neerincx (MSFT) — Mon, 02 Apr 2007 19:54:57 GMT

The username and password you pass in as -U and -P are SQL Server usernames and passwords, not Windows usernames and passwords.

So you cannot say:

sqlcmd -S machine_name -U machine_name\user_name -P user_password

If you want to log in as a specific Windows user, then you need to shell a command prompt as that user and use:

sqlcmd -S machine_name -E

You can shell a cmd prompt by using the RunAs command, like so:

C:\>runas /user:machine1\user1 cmd.exe

Enter the password for machine1\user1:

Attempting to start cmd.exe as user "machine1\user1" ...

This will open a new cmd window and from there you can run sqlcmd -S server -E

Matt

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

SQL Protocols — Mon, 02 Apr 2007 20:33:14 GMT

Hi, Sergei

The command line parameter " -U -P" is expected to use SQL Authentication which requires you log on as a SQL user. The SQL User can be a map of your windows account or non-windows account. In your case, you were using a machine account to access the DB. So, you can not use "-U -P", instead, do run as this machine account and execute "sqlcmd -S machine_name -E".

Or you can create a SQL login by

create login [login_name] with Password='...'

Then use sqlcmd -S machine_name -U [login_name] -P <pwd> to log on.

Good Luck!

Ming.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

faberyx — Wed, 04 Apr 2007 02:58:03 GMT

Hi everybody, ige this error when i try to connect to sql server from studio express

Error Number: 18456

Severity: 14

State: 1

Line Number: 65536

i can connect from visual studio and other softwares and from studio express on other machines. Does anyone know what the problem could be?

Fabrizio

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

SQL Protocols — Wed, 04 Apr 2007 05:01:22 GMT

Hi Fabrizio,

Have you looked at the server's error log and determined the error state reported by the server as outlined above?

Il-Sung.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Jacques — Mon, 16 Apr 2007 10:26:37 GMT

Hi All

I am having a similar problem where the state is 16. What is strange is that it occurs in the middle of a job and at intermittent intervals. The job would one day execute perfectly fine and the next day fail with error 18456 state 16. If you re-execute the job from the particular step that failed as the user that runns the job the job would succeed. The user that executes the job engine is a server admin and also starts all related SQL services. (SQL, SSAS, SSIS, etc)

Any advice?

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

EH — Mon, 16 Apr 2007 17:54:43 GMT

Hi, useful information, please add this to Books Online

Thx

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Derek — Wed, 18 Apr 2007 20:22:08 GMT

This article:

http://groups.google.com/group/microsoft.public.inetserver.iis.security/browse_thread/thread/68c216b10e7fa70/69aacf4a582ec20c%2369aacf4a582ec20c

They found a fix to the barrage of event log entries. Basically there is a setting in SQL Enterprise Manager's Tools > Options menu to regularly "Poll" the server to find out its state. Regardless of what auth method was used to register the server, it uses the client machine's user credentials to try to determine the state of the SQL Server's services. Unchecking the box will stop the error messages.

Still don't know WHY this happens, though - anyone?

Post in reply to:

Hi,

I have SQL 2000 SP 4 running with both SQL & Windows Auth. selected.

The moment I open Enterprise Manager from a computer that is not on the domain I get the following errors in the NT eventlog although I am using SQL Auth in Ent. Manager:

Logon attempt by: MICROSOFT_AUTHENTICATION_PACKAGE_V1_0

Logon account: [Remote NT User ID]

Source Workstation: [Remote Workstation Name]

Error Code: 0xC0000064

Logon Failure:

Reason: Unknown user name or bad password

User Name: [Remote NT User ID]

Domain: [Remote Workstation Name]

Logon Type: 3

Logon Process: NtLmSsp

Authentication Package: NTLM

Workstation Name: [Remote Workstation Name]

Caller User Name: -

Caller Domain: -

Caller Logon ID: -

Caller Process ID: -

Transited Services: -

Source Network Address: 10.2.5.16

Source Port: 0

I was under the impression that the whole point of SQL Auth. is not to try and Aut. the local logged on user?

Any pointers would be appreciated.

Adam

re: Understanding "login failed" (Error 18456) STATE 11

sig — Thu, 19 Apr 2007 05:57:44 GMT

I get this in the logs:

Error: 18456, Severity: 14, State: 11.

Did a scan through your blog page (very helpful, btw), but didn't see any recommendations for State = 11.

Any suggestions?

Thx.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Matt Neerincx (MSFT) — Thu, 19 Apr 2007 19:57:20 GMT

States 11 and 12 simply mean the Windows user coming in does not have login access to the server. SQL Server validated the Windows user's token, figured out who it is, but the Windows user has not been granted access to the server.

SQL Ranger - April 26th

...more notes from the field — Fri, 27 Apr 2007 09:25:38 GMT

Today it is all about security relating to SQL Server. The first session is being taught by a Software

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Doug — Mon, 07 May 2007 20:01:58 GMT

For State 11 - the login ID did not have a profile...I logged in to create the profile and all is right with the world!

Getting State 11 after server rebane on SQLAgent jobs

Bill — Tue, 08 May 2007 15:19:39 GMT

Hi. I renamed my SQL Server 2005 machine (within the same domain), and any jobs (e.g. backups) I try to run from Mgt. Studio are now getting:

05/08/2007 03:28:04,Logon,Unknown,Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [CLIENT: 10.70.40.55]

05/08/2007 03:28:04,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 11.

I have done the sp_dropserver/sp_addserver (w/ local) dance. This is a standalone server with no links. Service accounts are all using SYSTEM.

Any ideas what I can do to repair this?

e: Understanding "login failed" (Error 18461) error messages in SQL Server 2005

Satish K. Sharma — Sat, 19 May 2007 16:52:45 GMT

I m also facing the same problem while connecting the server in single user mode..

Error: 18461, Severity: 14, State: 1.

Login failed for user 'sa'. Reason: Server is in single user mode. Only one administrator can connect at this time. [CLIENT: <local machine>]

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Carl — Mon, 21 May 2007 05:27:44 GMT

Hi,

I tried to log in Database engine but it doesn't allow me to. It keeps giving me this message:

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: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.) (.Net SqlClient Data Provider)

I have no idea how to fix this problem. Could you please help me out? Thanks.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Andy — Mon, 21 May 2007 05:31:00 GMT

Hi. I have a problem with my connection. I checked the error log, it shows:

2007-05-19 22:19:27.47 Logon Error: 18456, Severity: 14, State: 11.

2007-05-19 22:19:27.47 Logon Login failed for user 'SQLServer\Andy'. [CLIENT: <local machine>]

Please help to solve this problem. Thanks so much.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

MING LU — Tue, 22 May 2007 01:28:23 GMT

Hi,Satish

Can you be more specific how you create the login and how you make connection by using which authentication, basically, the full connection string.

Thanks!

Ming.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Satish K. Sharma — Tue, 22 May 2007 15:14:59 GMT

The stiuation is:

Database server : SQl Server 2005

databse mode: Single user mode

user (used to connect to database server):

sa (for sql authentication), local admin level user for windows authentication

Error: Error: 18461, Severity: 14, State: 1.

Login failed for user 'sa'. Reason: Server is in single user mode. Only one administrator can connect at this time. [CLIENT: <local machine>]

utilities tried to connect to database server: MS SQL SERVER managment studio and sqlcmd (the command line utility)

please suggest on the same. if i m missing any thing or for any other detail feel free to contact...

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

rambo — Fri, 25 May 2007 00:39:59 GMT

See this link if your error is related to:

Re: The AcquireConnection method call failed with error code 0xC0202009.

http://forums.microsoft.com/msdn/showpost.aspx?postid=160340&siteid=1&sb=0&d=1&at=7&ft=11&tf=0&pageid=2

(Very Good)

Read the whole blog. The 'post 20' blog from akeiii solved my problem with running 32-bit apps on 64-bit SQL and for connection issues to MS Access 2003 databases (must run in 32-bit mode). Besure to look at changing Step package type to "Operating System (Cmdexe)" and entering a command string line like: "C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /FILE "D:\Projects\Fremont Dialer SSIS\DMFDailyDataFeed\DMF_Daily_Data_Feed\DMFDailyDataFeed.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E.

Also look at using the dtexecui.exe to help create the command string that appears after the DTExec.exe program above.

The DTExec.exe in the "program files (x86)" directory.

If you need more clarification or help email me on akmsweb@gmail.com

Was this post helpful ?

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Nitin — Sat, 26 May 2007 18:21:37 GMT

Ok,

I just installed Sql Server Deveplopment Edition along with SP2 on Vista. The login method chosen at the time of installation was Windows Authentication.

Installation was successful but I can not connect to Database engine and keep getting "Login failed for user" with State being 11 (sql error log). The sql server related services are running under LocalSystem account.

Any ideas?

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

IndusCreed — Sat, 26 May 2007 18:52:17 GMT

Figured out :)

The Sql Server 2005 needed to be run as Administrator. Dang it - Vista !!

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

nmadba — Wed, 30 May 2007 19:01:56 GMT

Error 18456

Severity 14

State 16

SQL 2005 SP2

Mixed Mode

Failed login is a domain account but the error message says the client is <local machine>

Also, the failed login successfully logs in many times during the day.

There are no error messages in the SQL Agent log, just the SQL Server logs. Profiler didn't pick up any unsuccessful logins(despite the login failure showing up every minute on the SQL Server logs). I've confirmed that the same user account successfully logins to all of the databases that it's mapped to. No user complaints, just tons of failed login attempts in the SQL logs.

I'm stumped.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Butt Crack — Thu, 31 May 2007 03:52:57 GMT

Wow, I can't believe I resolved this issue by luck!

I encountered this error on my local machine. So what I did was ...

1. Logon to SQL Server using windows authentication.

2. Right click in the query window that appears and select "Open Server in Object Explorer"

3. Go to object explorer and open the "Security" folder and then the "Logins" folder.

4. Double-click the "sa" user and change the password. Also, like another user mentioned above, untick the "Enforce Password Policy" in addition to resetting the password.

Now if you will all excuse me, I must go and play with myself.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Hubert Cumndedale — Mon, 04 Jun 2007 10:31:10 GMT

i like getting my ass licked by dirty whores.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Gary — Tue, 05 Jun 2007 00:44:29 GMT

I am getting this erro when trying to connect to a sql exoress 2005 db throught vb.net on one of my pc's but not the other.

Error: 18456, Severity: 14, State: 16.

Assuming it is a permission problem, I created another DB on the PC that I cannot connect with & still cannot connect through the program. ANy suggestions would be appreciated. (I can be reached at gtinkel@profsft.com.

Thanks

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Alex — Tue, 12 Jun 2007 16:55:06 GMT

Hi,

In one of our test environments we can connect locally through ODBC, but cannot connect from a vmware image or any remote box. This is a mixed mode SQL Server 2005 and we have a sqlserver user which we are using to connect (it is dbowner of all database

We have set Show_errors in the main db service to 1 and restarted, but we do not see the state (another machine on the network does on their own copy of SQL2005SP1). We also have the setting "Audit login errors only", but nothing gets added to the ERRORLOG.

How do we set this debug?

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005 - State 11

Campbell — Tue, 19 Jun 2007 12:37:56 GMT

I had an "Error: 18456, Severity: 14, State: 11." in the log, meaning SQL was authenticating ok, but Windows was not. I am running Windows Vista. It turned out I needed to right-click on my app and run as Administrator. It now works fine.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Olivier — Wed, 20 Jun 2007 10:35:19 GMT

Hi,

We use Sql Server 2000 SP4 on Windows 2000 SP4. The audit level is set to login failure for this server but we don't get any state informpation in the log file. here is an extract of the log file :

2007-06-19 15:45:02.45 logon Échec de la connexion de l'utilisateur 'sa'.

2007-06-19 15:45:22.17 logon Échec de la connexion de l'utilisateur 'sa'.

2007-06-19 15:45:35.07 logon Échec de la connexion de l'utilisateur 'sa'.

2007-06-19 15:46:21.01 logon Échec de la connexion de l'utilisateur 'sa'.

Do you have an idea why I don't see any "state information" in the log file. I need this information to understand why I get this connection error.

By the way, the connection is OK 99% of the time and Sql Server is used by an ASP web application.

Best regards,

Olivier

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Kevin — Sat, 23 Jun 2007 00:15:46 GMT

I am getting this erro when trying to run jobs in SQL Agent. I have confirmed the login account used to start Agent is valid. The account also has sysadmin privs....Stumped...

Error: 18456, Severity: 14, State: 16.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

soumya — Wed, 04 Jul 2007 11:19:05 GMT

I tried a number of "strangies", but this one worked on more than one occasion: using the direction arrow key pointing left, I moved the cursor back to the beginning of the password. It worked!

Try this at home, folks, it does not hurt a bit, and perhaps it might give the Microsoft boys a hint about the nature of the problem if you report the results.

thanks bertie,this worked for me

Connection to a SQLServer Express 2005 via JDBC

Ralle's personal blog — Thu, 05 Jul 2007 09:10:30 GMT

I discovered a few stepstones when connecting via JDBC to a locally installed SQLSever Express 2005 database. Here are my observations:Activate "Accepting TCP/IP" connections in the SQL Server Configuration Manager. Otherwise the SQLServer Expr

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

SQL Protocols — Thu, 05 Jul 2007 20:06:12 GMT

Ralle,

Yes, you are correct. The Microsoft SQL Server 2005 JDBC driver requires SQL Server (any SKU) to have TCP/IP support enabled.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Gregg — Sun, 08 Jul 2007 22:17:05 GMT

I am also getting Error: 18456, Severity: 14, State: 8 when my .NET 1.1 app attempts to log into the 2005 Server from a remote machine. The app works against a 2000 Server. The password is correct, as the same login and password can be used to log into the 2005 server from the same remote machine with SSMS. The State: 8 is either bogus or too generic to be useful. Any help?

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

jaap@myprovider.com — Wed, 11 Jul 2007 19:16:12 GMT

exec sp_password @new = 'sqlpassword', @loginame = 'sa'

alter login sa

with password = 'sqlpassword' unlock,

check_policy = off,

check_expiration = off

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Gregg — Fri, 13 Jul 2007 19:53:11 GMT

Jaap@myprovider.com-

THANKS! Don't know why that worked, but it did, and I thank you.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Hermann Rösch — Wed, 18 Jul 2007 00:14:22 GMT

In Windows Vista: All Programs -> Microsoft SQL Server 2005 -> (Right Click) SQL Server Management Studio -> Run as administrator

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

MING LU — Wed, 18 Jul 2007 01:02:28 GMT

Hi, Hermann

What operation you did after you click SSMS? It seems you need to first type the target server name and credential to login then connect.

Can you provide the error message say " 18456 Level ?? State ??" to help us identify the problem.

Thanks!

Ming.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

SQL Protocols — Wed, 18 Jul 2007 01:44:37 GMT

Hi, Hermann

Please check out the following blog: http://blogs.msdn.com/sql_protocols/archive/2007/06/18/connecting-to-sql-server-2005-on-vista-and-longhorn.aspx

or install Yukon SP2, find out the article in Books online "How to: Connect to SQL Server from Windows Vista" for the issue understanding and resolution.

Good Luck!

Ming.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

SQL Protocols — Wed, 18 Jul 2007 01:44:38 GMT

Hi, Hermann

Please check out the following blog: http://blogs.msdn.com/sql_protocols/archive/2007/06/18/connecting-to-sql-server-2005-on-vista-and-longhorn.aspx

or install Yukon SP2, find out the article in Books online "How to: Connect to SQL Server from Windows Vista" for the issue understanding and resolution.

Good Luck!

Ming.

» ???????????? “login failed” (Error 18456) ?? Microsoft SQL Server 2005

» ???????????? “login failed” (Error 18456) ?? Microsoft SQL Server 2005 — Thu, 02 Aug 2007 10:37:47 GMT

PingBack from http://sql.khanzhin.info/?p=9

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Dave — Wed, 08 Aug 2007 06:29:51 GMT

I'm getting Error: 18456, Severity: 14, State: 16 at startup. I have a windows service that depends on SQLServer (Express) and tries to login using the 'Transactor' account. This fails at startup but I can login and start the service manually and it's fine. I would really appreciate some help with this, it wouldn't be a stretch to say I'm floundering. ERRORLOG follows:

2007-08-08 14:18:39.25 Server Authentication mode is MIXED.

2007-08-08 14:18:39.25 Server Logging SQL Server messages in file 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.

2007-08-08 14:18:39.25 Server This instance of SQL Server last reported using a process ID of 2260 at 8/08/2007 2:14:43 p.m. (local) 8/08/2007 2:14:43 a.m. (UTC). This is an informational message only; no user action is required.

2007-08-08 14:18:39.25 Server Registry startup parameters:

2007-08-08 14:18:39.25 Server -d c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf

2007-08-08 14:18:39.25 Server -e c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG

2007-08-08 14:18:39.25 Server -l c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf

2007-08-08 14:18:39.28 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.

2007-08-08 14:18:39.28 Server Detected 2 CPUs. This is an informational message; no user action is required.

2007-08-08 14:18:39.43 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.

2007-08-08 14:18:39.50 Server Database mirroring has been enabled on this instance of SQL Server.

2007-08-08 14:18:39.50 spid5s Starting up database 'master'.

2007-08-08 14:18:39.64 spid5s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.

2007-08-08 14:18:39.78 spid5s SQL Trace ID 1 was started by login "sa".

2007-08-08 14:18:39.79 spid5s Starting up database 'mssqlsystemresource'.

2007-08-08 14:18:39.79 spid5s The resource database build version is 9.00.3042. This is an informational message only. No user action is required.

2007-08-08 14:18:39.96 spid5s Server name is 'TXWC02'. This is an informational message only. No user action is required.

2007-08-08 14:18:39.96 spid5s Starting up database 'msdb'.

2007-08-08 14:18:39.96 spid8s Starting up database 'model'.

2007-08-08 14:18:40.09 spid8s Clearing tempdb database.

2007-08-08 14:18:40.32 Server A self-generated certificate was successfully loaded for encryption.

2007-08-08 14:18:40.32 Server Server is listening on [ 'any' <ipv4> 1433].

2007-08-08 14:18:40.32 Server Dedicated administrator connection support was not started because it is not available on this edition of SQL Server. This is an informational message only. No user action is required.

2007-08-08 14:18:40.32 Server The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b, state: 3. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.

2007-08-08 14:18:40.32 Server SQL Server is now ready for client connections. This is an informational message; no user action is required.

2007-08-08 14:18:40.53 Logon Error: 18456, Severity: 14, State: 16.

2007-08-08 14:18:40.53 Logon Login failed for user 'Transactor'. [CLIENT: 10.20.1.179]

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Erik — Thu, 09 Aug 2007 03:55:28 GMT

Campbell, thanks! for state 11, running as administrator worked.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Matt Neerincx (MSFT) — Fri, 10 Aug 2007 01:26:11 GMT

This can also happen if for example the default database for user FOO is not online (for example the database is marked suspect).

So to check on this theory, try logging the user into some other database (like master) and then try using the USE DATABASE command to switch to the target database, you will get a better error message as to the root cause of the failure.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Dave — Fri, 10 Aug 2007 06:53:50 GMT

Matt I'm assuming your comment is directed at my post. I understand what State=16 means, the issue is that it is only occurring when the machine is booting. My Windows service has a dependency on SQLServer so starts only after SQLServer has started. However I consistently get a login failure at boot time but when I subsequently login the Windows service can be started manually without any problem. I guess what I'm looking for is feedback on whether there is a workaround for this issue. I have seen a number of posts on the web about the issue but I haven't yet seen a good solution. The one solution I have seen is modifying the dependent service to loop with a delay for a period while trying to connect at startup. This is not a solution that will work for my environment as the Windows Service is a third party product (and no I cannot go back to the vendor and ask them to insert a delay at startup).

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005 state 5

Keith Hobbs — Thu, 16 Aug 2007 15:48:05 GMT

We have a new server running win 2003 and sql server 2005. Our users have an Access database that contains ODBC linked tables to the sql 2005 db.

The windows users belong to an active directory security group and this group has been granted access to the database that Access is using.

The server log is consistently showing the 18546 error with state 5 indicating invalid user? Users are quite happy and the underlying tables are being updated. I've been looking at this all day now and can see nothing obvious wrong.

I'm new to sql 2005 so any help would be greatly appreciated.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Matt Neerincx [MSFT] — Thu, 16 Aug 2007 19:24:53 GMT

This can happen for example if your company has auditing software running and checking if your SQL Server has weak passwords. I see this periodically on my network. Our IT group has a process that scans for SQL Servers and then attempts to log into these using weak sa passwords to detect insecure SQL Servers.

Look at the source ip address and run ping -a <ip> to determine the source of the requests.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Karen Bryan — Wed, 05 Sep 2007 12:06:41 GMT

Hi,

We've currently in the process of changing web hosts, and are having to move our databases to SQL Server 2005. In a trial run we tried to copy a database from our local SQL Server 2000 development machine to our hosted SQL Server 2005 machine. We got a 'login timeout' error when the package was being built. So, having installed SQL Server 2005 Developer edition on my local machine we're running tests trying to use the copy database wizard to copy a database between two SQL Server 2005 machines. We've had ports opened on both firewalls for this traffic, and have ensured that remote connections are allowed. We've located the error logs, and the following error is listed:

Error: 18456, Severity: 14, State: 8.

Now, thanks to this article I understand that this is a password mis-match, but what I don't understand is why! The passwords have been entered correctly (including case). I've tried changing the password via the Management Studio, and other suggestions here, but still no joy. This is delaying our migration of sites and databases. If we just import the data from the existing server to the new one we lose all Primary Key information, and any defaults set for certain fields - information we need to retain as we bring the databases back to the local machine on a regular basis as part of our backup strategy. Can you help us to get this working?

Thanks

Error: 18456, Severity: 14, State: 8

Locke_ — Mon, 17 Sep 2007 11:23:04 GMT

I received this error message also after deleting/renaming the default database of the login concerned. After resetting the default database of the login, it's fine.

EDIT: Error: 18456, Severity: 14, State: 8

Locke_ — Mon, 17 Sep 2007 13:42:07 GMT

...or if the default database is not set.

(SQL 2005 Server Manager, Connect to Server with Admin --> Object Explorer --> Server --> Security --> Logins --> context menu on Login concerned --> Properties --> General

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Martin — Wed, 03 Oct 2007 22:02:28 GMT

Although this blog is the most helpful source I detected so far, I can't find any helpful solution on

Error: 18456, Severity: 14, State: 16.

Problem is that we experience this error only occasionally, so I guess it's a timing problem. With other words: connections with exactly the same ODBC connection fails, some seconds later it works perfectly. Therefore it's not related to lack of rights and the errlog's don't show any hint that the DB is marked suspect.

Any advices very warm welcome.

Best regards, Martin.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Anon — Fri, 05 Oct 2007 11:25:48 GMT

I just want to say Thank you

Your table lead me straight to the source of my issue and I was able to fix it within 2 minutes.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

vramakrishna_t — Wed, 10 Oct 2007 18:33:34 GMT

We have deleted one of the sharepoint portal from our sharepoint server along with the database. after deletion of the portel, we are continiously receiving the below event in our SQL 2005 server every 5 minutes.

2007-10-10 20:54:18.35 Logon Login failed for user 'SERWIZSOL\Svc-Sharepoint'. [CLIENT: 10.64.130.160]

2007-10-10 20:54:18.37 Logon Error: 18456, Severity: 14, State: 16.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005 state 1 severuty 14

Gurumurthy — Sat, 13 Oct 2007 09:12:32 GMT

The below message is the one I got when I tried to connect using Sql server 2005. Could anyone please try to help me to sort it out

===================================

Cannot connect to GURUMURTHY-PC.

===================================

------------------------------

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476

------------------------------

Server Name: GURUMURTHY-PC

Error Number: 18456

Severity: 14

State: 1

Line Number: 65536

------------------------------

Program Location:

at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)

at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)

at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)

at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)

at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)

at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)

at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)

at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)

at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)

at System.Data.SqlClient.SqlConnection.Open()

at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server)

at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Terry Brown — Thu, 18 Oct 2007 01:26:45 GMT

I am getting an Error: 18456, Severity: 14, State: 11. when my users are triing to make an ODBC connection to SQL 2005 STD from Acess 2007. I have sqitched the SQL server from Windows Authentication mode to SQL server and Windows Authentication mode, this did not help. Any ideas? Thank you.

Ghillie Suits » SQL Protocols : Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Wed, 24 Oct 2007 09:33:19 GMT

PingBack from http://ghillie-suits.info/?p=23716

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Nimish — Fri, 02 Nov 2007 15:19:08 GMT

I have added the sqlserver 2005 instance using sp_addlinkedserver. And when I try to use the linked server inside my query it says login failed for user 'sa'. When I checked at the error log on remote server(where the SQL Server 2005 is installed) the State was 8.

But I am able to connect to the SQL Server instance from Mgmt Studio Express and also using openrowset distributed queries.

Can I know the actual problem?

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Ken — Wed, 07 Nov 2007 23:01:57 GMT

I am getting Error 18456 State 8 sporadically for many users. Sometimes they can log on OK, and sometimes not, using the same password. The logins and passwords were migrated from SQL2000 using sp_help_revlogins.

If I change the password, they can log on using the new password, but later that same new password is rejected. If they try again later, it may work again!

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Geo — Wed, 14 Nov 2007 02:26:35 GMT

I'm having the same problem as Ken. My databases were restored to sql 2005 server and are in 2000 compatibility mode. Usually the logins work but occasionally for no apparent reason I get Error 18456 State 8. I know the password is correct as it is coming from the config file and not changing.

I tried deleting the users and recreating them by hand but still have the same sporadic problem.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Geo — Wed, 14 Nov 2007 02:28:22 GMT

SQL Server build is 9.0.3159 by the way

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Dominique — Wed, 14 Nov 2007 20:58:50 GMT

Hello,

I have the same error. What is stange is that the ODBC connection was working last week when I used it and today when I tried again it failed... Any corruption?

Thanks,

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Dominique — Thu, 15 Nov 2007 23:07:05 GMT

Hello

I have the error 18456 with any users when one specific user is login in to the PC only???

Test1 on PC 1:

User A log to the PC

ODBC with UserA is fine

ODBC with UserB is fine

User B log to the PC

ODBC with UserB failed error 28000-18456

Test1 on PC 2

User A log to the PC

ODBC with UserA is fine

ODBC with UserB is fine

User B log to the PC

ODBC with UserB is fine

What is going on???

Thanks

Dominique

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Nameless — Mon, 19 Nov 2007 13:12:52 GMT

We have this strange Error:

[298] SQLServer Error: 18456, Login failed for user 'Domain\DOMAINAdministrator'. [SQLSTATE 28000] We have windows authentication turned on this happens on a MOSS Server with SQL Server 2005

re: Understanding "login failed" error messages in SQL server 2000

hello — Wed, 21 Nov 2007 23:50:20 GMT

hi all,

i have encountered this login failed error with State 1 using SQL Server 2000. What does state 1 indicate?

While using the server explorer i had to make a connection where i encountered this error message.

Please mail me the replies asap to dency_opus911@yahoo.com

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005 state: 12

Dissonance — Mon, 26 Nov 2007 10:05:00 GMT

Hello, I have read technet forum about this error but I still have no answer about state 12. I have tried to give all permissions that I know for 'testlogin' except sysadmin and it has no effect! Please help!

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Rob — Wed, 28 Nov 2007 03:20:13 GMT

I have an Error -18456 but no state level given. It just states Login failed to user.

Microsoft SQL server Error-18456.

I am trying to get access to sql server authentication mode. While I am able to get access to windows authentication mode.

I made shure to choose Mixed authentication mode while installing my sql server 2005 software.

Can any one help me thanx.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Rob — Wed, 28 Nov 2007 03:20:14 GMT

I have an Error -18456 but no state level given. It just states Login failed to user.

Microsoft SQL server Error-18456.

I am trying to get access to sql server authentication mode. While I am able to get access to windows authentication mode.

I made shure to choose Mixed authentication mode while installing my sql server 2005 software.

Can any one help me thanx.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Simon Larsen — Wed, 28 Nov 2007 11:07:05 GMT

I think you will find that you have a sql security group or user which has a default database set which it no longer has access to.

If you look in the logs are you getting a successful login followed immediately by a failed login? that's what I had and it was a local group that the user was a member of which had that error. Gave public access to the db and done.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

bfinley — Wed, 28 Nov 2007 23:45:17 GMT

All I just received the same error and found the problem was related to Reporting Services. You may want to open SQL Server Configuration Manager, shutdown reporting services and retry the sqlcmd.

I am not certain if this has been mentioned. I am running Enterprise on Win2003 server.

Good luck.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Francisco Rodriguez — Fri, 07 Dec 2007 02:59:29 GMT

Hi everybody, we had a "State 16" problem with one of our databases in an ASP.NET app running in a SQL Server 2005 box. In the SQL Server error log we found this:

2006-10-31 08:58:36.01 Logon Error: 18456, Severity: 14, State: 16.

2006-10-31 08:58:36.01 Logon Login failed for user '<appuser>'. [CLIENT: <server>]

Following comments from this blog entry we checked <appuser>'s login in server, and <appuser>'s user in database, and they were ok. We also checked schemas in database and we found that one of them had a nonexistent owner, that is, the owner of the schema was not a valid login in the server. This happened because we moved the database from another server, where the owner was a valid one.

So we changed the owner of the schema to dbo, and that was it! State 16 error was gone and our app worked perfectly.

I hope this tip will help anyone.

Cheers,

FRodriguez

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

beezel — Mon, 10 Dec 2007 22:20:09 GMT

Holey Moley! Bertie's solution worked for me!

arrow over to the beginning of your password text box.

I tried a number of "strangies", but this one worked on more than one occasion: using the direction arrow key pointing left, I moved the cursor back to the beginning of the password. It worked!

Try this at home, folks, it does not hurt a bit, and perhaps it might give the Microsoft boys a hint about the nature of the problem if you report the results.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

vee — Thu, 13 Dec 2007 06:32:54 GMT

I get the error code 18456 connection failed then 2000 for one of my employees but it only occurs when loggin in to one specific computer. She can log in under all other computers.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Kraig — Thu, 13 Dec 2007 17:09:32 GMT

Just FYI for those getting the State: 16 error. After beating my head against the wall checking permissions, DB owners, etc. I found one old SQL Agent job that was trying to run for a database that no longer existed. Everytime the job tried to run it gave this error, all I had to do was delete the job. So it's not always something complicated.

Error State : 11

pietjegates — Thu, 13 Dec 2007 18:11:35 GMT

I receive this error in the following scenario:

A user account had acces to 2 databases on the same SQL server via windows group membership.

I deleted the first database (and the corresponding log in) between the time the user had logged on to the domain and the starting of the "SQL server management studio". The connection to the SQL Server was made trough the memebership of the group of wich the login was deleted.

Solution: User logged off and on (windows) and the correct group was used to connect to the SQL server.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Dan — Thu, 20 Dec 2007 16:53:46 GMT

Re all the questions about state 16, I don't know if they are related but I was seeing intermittent state 16 errors, but just before they started a transaction log full error was written to the log. Truncating and shrinking appears to have solved this.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

AbTseg — Fri, 21 Dec 2007 18:32:00 GMT

Please I need your help

Thank you!

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Jaime — Fri, 28 Dec 2007 01:17:21 GMT

Hi,

we are getting this error with Serverity: 14 and State: 8. We don't understand the reason why this error only has appear today but with a several number of users. Could someone help us?

Thank you!

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Mach — Thu, 03 Jan 2008 23:50:38 GMT

Hi,

I keep getting this error too. But I can't get into the error log file to see what state it is. It keeps telling me that access is denied. I've even created an everyone permission with full control and that didn't fix my issue. Then I created a whole new username/login and that keeps getting the same results. Any suggestions?

I'm running Windows Server 2003 & SQL Server 2005.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Matt Neerincx (MSFT) — Fri, 04 Jan 2008 03:50:12 GMT

Ok, if you cannot start SQL Server AND you cannot access the ERRORLOG file using notepad, then I suspect something else on your system has the ERRORLOG file locked and this is bad and needs to be fixed first.

It could be something in the file system like a virus scanner doing this. First thing I would do is set the SQL Server service (in Services) to manual, then reboot the machine. After reboot, but before attempting to start SQL, see if you can open the ERRORLOG file using notepad. Let me know how this goes.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Mach — Fri, 04 Jan 2008 15:43:28 GMT

Alright, we've fixed the problem with connecting with SQL Server. User can now successfully Open and connect using Management Studio. I checked the directory for where the error log should be but when I hover my mouse over the folder, it tells me that the folder is empty (and I can't access it). Just to double check, does this sound like a correct path for the errorlog:

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\ERRORLOG [Should be here]

Because it's not there.

Thanks for your help by the way.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

nins — Fri, 11 Jan 2008 14:24:02 GMT

I created a login and assigned it to the sysadmin role on my local server, after which I restared the server. Also, I changed the max number of concurrent users allowed to 1. Now when I try connecting to the server, I get the error message, 'Connection was successfully established with server but an error occured durin the login process'. When I check the error log the following is noticed "Error: 17809, Severity: 20, State: 3. Could not connect because the maximum number of '1' user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed." I tried changing the max number of user connections allowed by using sqlcmd utility. In the command prompt it says value changed from 1 to 10, but when i try to log in to the server i get the same old error.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Peter — Wed, 16 Jan 2008 07:01:04 GMT

Where can I find a complete list of Error State for Error 18456? If there is no such list, I hope the table in this blog will be updated to include common error states such as State 16.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Peter — Wed, 16 Jan 2008 07:20:07 GMT

I can duplicate Error State 5 and 8. How to duplicate Error State 2 and 9?

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Luis Rivera — Wed, 16 Jan 2008 20:15:50 GMT

01/15/2008 12:11:53 ERCreateSiteWizard[16f4:17d0]: ----Storage,0: [Microsoft][ODBC SQL Server Driver][SQL Server]Error de inicio de sesión del usuario 'sa'. [18456]

Error: 18456, Severity: 14, State: 8

Errror this does not allow me to connect to SQL Server 2005.

This connection is made by COM ports. Someone has an idea?

my e-mail: luis.rivera@nasoft.com

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Matt Neerincx (MSFT) — Thu, 17 Jan 2008 03:06:53 GMT

I'll try to get the full list posted here.

Also, you guys know about the security ring buffer in SQL 2005 SP2, read this:

http://blogs.msdn.com/lcris/archive/2007/02/19/sql-server-2005-some-new-security-features-in-sp2.aspx

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Matt Neerincx (MSFT) — Thu, 17 Jan 2008 03:21:23 GMT

State #8 means the password did not match. So just an invalid password.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Hans Esquivel — Fri, 25 Jan 2008 01:54:07 GMT

I believe we have an answer for the Severity: 14 and State: 16, while using SharePoint Server 2007.

While attempting to enable SSO for the first time, if you fail to configure the Microsoft Single Sign-on Service with the proper 'Service Account' you will get an error. It seems that SharePoint creates a job looking for the 'SSO' database, even though it failed to create it - reason for State:16.

Steps we took to correct the issue:

1. Opened Windows Services

2. Configured Microsoft Single Sign-on Service to use the proper account

3. Opened Central Administration >> Operations >> Manage settings for single sign-on

4. Configured properties to use the same account used for Microsoft 'Single Sign-on Service'

The database (SSO) was created successfully and all the Error: 18456 went away. Hopefully this works for all of you that were experiencing the same issue.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

kiks — Tue, 29 Jan 2008 20:59:50 GMT

Server Name: jmsvsqlp40.corp.jmfamily.com,51321

Error Number: 18456

Severity: 14

State: 1

Line Number: 65536

HELP!!!!

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

kiks — Tue, 29 Jan 2008 21:00:07 GMT

Server Name: <server name>

Error Number: 18456

Severity: 14

State: 1

Line Number: 65536

HELP!!!!

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Zorya — Fri, 01 Feb 2008 17:32:26 GMT

Hi,

After we upgrade some dev instances to SQL 2005 SP2 build 3215 i got the error when trying to connect with SQL authentication (Windows Authent is working for me, but not for others). I cannot assure somebody didnt type a wrong password.

But after a while, everything is working fine again....

Is there a process that locks the connection on an instance after a failed logon attempt ????

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

gy — Sun, 03 Feb 2008 23:15:10 GMT

If you are running it on Vista, select "Run as Administrator" by pop-up menu (right click).

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Ollin — Mon, 04 Feb 2008 15:29:54 GMT

After looking for a solution for my problem with state 11 for a while I have several times stumbled over a solution for you who uses Vista and XP as your platform for SQL.

Might be helpful for other states as well.

-Turn off "simple file-sharing" using Control panel->folder options-> view

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Frodoger — Wed, 06 Feb 2008 19:21:16 GMT

We have also a login problem but its sporadic.

We use SQL Server 2005 SP2 on Windows 2003. The Client use ODBC through SQL Server native Client.

What can we do to search a solution? Its important for use because every time we get this error the Client application crash.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Frodoger — Wed, 06 Feb 2008 19:41:41 GMT

plz delete and forgot my reply. i have not complete read my error picture. sorry.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Asif Bawany — Sat, 09 Feb 2008 00:52:18 GMT

sql server configuration manager - Enable TCP/IP and named pipes; by default these settings are disabled because of security settings; I tried and it worked for me :)

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Mark Brinton — Tue, 12 Feb 2008 21:07:50 GMT

Error: 18456, Severity: 14, State: 16.

I found the other posts very helpful. Using Windows authentication on a local admin account which had sql 2005 sysadmin privileges, our install scripts would intermittently fail. The scripts would detach, copy and attach mdfs from one server to a different server. Sometimes it worked. Sometimes it failed.

What needed to be done is to refresh/resynchronize Server metadata with database metadata. Critically important, it seems, is to re-establish a link between the database owner of the newly attached db to an existing server login.

The upgrade scripts were obviously erroneous in this regard and this bug, which existed for YEARS and was latent from versions SQL 7.0 through SQL 2000, decided to announce its presence in the data migration from SQL 2000 to SQL 2005.

Thanks to everyone who have taken the time to contribute. It helped alot.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

ccardwell — Tue, 26 Feb 2008 23:07:18 GMT

Here's a different twist. I am running SQL Server 2005 and using Netapp storage devices for the databases. As a part of the configuration I have a seperate connection to the netapp devices using a VLAN on the switch. This connection is where all communications between the netapp and the windows OS happens for controlling disk IO. I just discovered that my System Administrator account is attempting to authenticate over the Netapp data connection rather than over the primary LAN connection. The only other database that I have running seems to be working just fine. I was attempting to install a new application which would fail when it tried to create the database due to a login failure. The 'data' vlan is not routed at all so it was unable to authenticate against the DC and the install/create db failed. Is there a way to tell SQL Server to only use the primary LAN connection to authenticate with the DC?

This is a rather truncated question as I realize that I have left out a lot of detailes - this is in the interests of brevity. If anyone has any ideas about this and would like more information I will provide more details as necessary. Basically I have two NICS functioning on two diferent Networks and want to be sure that SQL server only looks to a specific NIC/LAN for authentication.

Thanks in advance for any assistence someone/anyone can provide.

Charlie

SQL 2005 Linked Server Problems... Error: 18456, Severity: 14, State: 11

Patrick S — Fri, 29 Feb 2008 18:30:05 GMT

Hello,

I have a question. We have two SQL 2005 boxes in our dev environment. Our Active Directory is housed on a SBS 2003 server, which has recently been rebuilt. After we brought the rebuilt SBS server back online, we began to see weird permissions issues. Currently, our SQL boxes use Windows Authentication and Kebros.

The problem we're experiencing (seemingly after the SBS 2003 server rebuild) was that many of our linked server objects between the two SQL servers began to fail.

The errors we're seeing are:

The Linked Server properties are set to use the "login's currentl security context", meaning you should be connecting across servers as yourself (windows authentication). All of this worked last week, but after the SBS rebuild its all failing. Any ideas?

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

SQL Protocols — Tue, 04 Mar 2008 21:26:38 GMT

Hi, Patrick

I would recommend you check out Nan's blog entry on this error for linked servers:

http://blogs.msdn.com/sql_protocols/archive/2006/08/10/694657.aspx

Since you recently rebuilt your Active Directory server and don't indicate that you changed anything in the SQL Server instances, I would first:

1. Make sure that the Active Directory accounts that are used to log in to SQL Server do not have the following property set in Active Directory: Account is sensitive and cannot be delegated

2. Make sure that the Active Directory accounts that each SQL Server service runs under are trusted for delegation in Active Directory.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

SQL Protocols — Tue, 04 Mar 2008 21:27:08 GMT

Hi, Patrick

I would recommend you check out Nan's blog entry on this error for linked servers:

http://blogs.msdn.com/sql_protocols/archive/2006/08/10/694657.aspx

Since you recently rebuilt your Active Directory server and don't indicate that you changed anything in the SQL Server instances, I would first:

1. Make sure that the Active Directory accounts that are used to log in to SQL Server do not have the following property set in Active Directory: Account is sensitive and cannot be delegated

2. Make sure that the Active Directory accounts that each SQL Server service runs under are trusted for delegation in Active Directory.

Good luck!

Dan

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Patrick S — Tue, 04 Mar 2008 23:15:31 GMT

Dan,

Thanks. I did just realize something, the links work when I remote desktop into the server and check the link to the other server... They fail when I use SQL studio on my desktop and test the link objects. That's a double-hop versus a single-hop, mentioned in the article you reference.... which makes it a Kebros problem I'm thinking. I'm going to keep digging. Let me know if you have any more ideas, and thanks for responding!

Patrick-

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Sandy — Wed, 05 Mar 2008 19:17:56 GMT

I have 18456 alternating between state 8 and 16.

If I look at the user in my C# connection string in SQL Studio management, the password always changes to a default 15 chars password however many times I change it. The user's status is locked out and the checkbox is greyed so I can't change it.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

SSG — Thu, 13 Mar 2008 20:59:39 GMT

For State 11 errors the Domain account (Domain\WinNTlogin for example) was accessing SQL Server fine and then at some point we experienced the State 11 errors (maybe related to Mirror Failover or some other issues on the server). I had to drop the Account from the Local Admin group and this stopped the errors occurring and then I added the account back in to the Local Admin group and errors did not return.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

brad — Fri, 21 Mar 2008 19:53:08 GMT

thanks for a well written instructions

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Steve Barnes — Mon, 14 Apr 2008 18:19:10 GMT

State 16 may also mean the database name is not correctly "cased". Yes, believe it or not, the SQL server on my dev box would accept either case "dbname" or "DBNAME" in the connection string. The SQL server on my Win2003 Server, on the other hand, demanded the exact case spelling as when the database was created. Both database servers are at SP2.

Again, Il Sung's suggestion to test connectivity via the USE <DATABASE> command was the key to a more relevant error message.

Related to

Error: 18456, Severity: 14, State: 16

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

JP Roberts III — Tue, 15 Apr 2008 17:14:37 GMT

This wasn't the exact answer, but it led to my solution. Thanks!

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

STan — Sat, 19 Apr 2008 22:01:43 GMT

Сцуко!

Нихуя не помогли ваши хуевые советы!

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Torsten — Wed, 30 Apr 2008 14:37:10 GMT

Hi there,

I've written a service that connects to a SQL-Server 2005 (Express) database. The service works fine but two times a day I receive an error:

Error: 18456, Severity: 14, State: 16

The error mostly occurs at 9:30 pm and 2:00 am but not always. May a backup-program cause the problem. I read that maybe the database is offline at the moment, but how could this be if this were the reason? After getting this error, the service itself tries to connect to the db as long as neccessary, and so I was able to see in the log files that the db is not available for just about 1 min, after this period of time the service is able to reconnect to the database again. I also found an article describing how to disable a service-job that cleans up no more used sessions, but no jobs are running on the sql-server.

Let me know if you have any ideas to solve the problem.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

SQL Protocols — Wed, 30 Apr 2008 20:53:39 GMT

Is the SQL Express offline at that time? If so, it's a by-design behavior for SQL Express. Please check this forum post see if it applies to your case. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=309672&SiteID=1

Thanks,

Xinwei

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Torsten — Fri, 02 May 2008 12:52:48 GMT

Hi Xinwei,

no, I don't think the SQL-Express is offline or idle at that time, nothing in the log implies to that:

2008-04-30 01:49:51.89 spid51 Starting up database 'wtimserv'.

2008-04-30 01:51:07.48 spid51 Starting up database 'wtimserv'.

2008-04-30 01:53:38.28 spid51 Starting up database 'wtimserv'.

2008-04-30 01:54:53.98 spid51 Starting up database 'wtimserv'.

2008-04-30 01:59:56.93 spid51 Starting up database 'wtimserv'.

2008-04-30 02:11:13.81 Error: 18456, Severity: 14, State: 16

2008-04-30 02:11:13.81 Login failed for user 'wtime'. [CLIENT: <local machine>]

2008-04-30 02:11:16.81 spid51 Starting up database 'wtimserv'.

2008-04-30 02:14:58.34 spid51 Starting up database 'wtimserv'.

2008-04-30 02:18:45.73 spid51 Starting up database 'wtimserv'.

2008-04-30 02:20:01.32 spid51 Starting up database 'wtimserv'.

2008-04-30 02:23:47.46 spid51 Starting up database 'wtimserv'.

hmmm... or is it idle and the "Starting up database" - Message is a keep alive signal?

[MsSQL]Ms Sql 2005 - MasterDrive.it - Information Technology Developers Community

Mon, 19 May 2008 00:00:29 GMT

PingBack from http://forum.masterdrive.it/database-48/mssql-ms-sql-2005-a-26335/#post125491

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Imran Taher — Wed, 28 May 2008 17:09:07 GMT

I got this message again and again, to say my sql server is full of this error... Login failed for user 'NT AUTHORITY\SYSTEM'. [CLIENT: <local machine>]

As suggested I checked Sql Server logs which says

Error: 18456 Severity :14 State :16

Recently I took some of the databases offline which we were not using. But when I checked that login 'NT AUTHORITY\SYSTEM' has access to two databases which were taken offline I brought those two offline databases online, I hope the error might not occur again, and as of now I did not see any error message. Still fingures crossed.

Thanks for the information.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Concerned Programmer — Fri, 13 Jun 2008 02:27:41 GMT

Where can I find the server's error log?

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

SQL Protocols — Fri, 13 Jun 2008 19:30:14 GMT

By default, the error log is located at Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG and ERRORLOG.n files.

HTH

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

SteveH — Mon, 16 Jun 2008 14:57:39 GMT

My Severity 1 fix:

I had to use SQL Server 2005 (Developer Edition) Surface Area Configuration and then "Add New Administrator" granting myself privileges on the next screen to log in (using Windows Authentication). Then I was good to go. Easy once you figure it out. I guess they were paranoid and didn't want to automatically assume that the person installing it would actually want to log in as administrator (?).

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Patric — Fri, 27 Jun 2008 04:34:42 GMT

Hey Guys,

what is State 4 all about?

Short information:

We recently get the Error:

18456 State 4

- SQL Server 2005 Developer 64 Bit

- Windows Server 2008 Enterprise 64 Bit

Well intersting Thing here is, that just the Active Directory Administrator can Login to the Server. We tryed several things:

User with SQL Admin rights. -> No Effect

Just the Active Directory Admin and Local Administrator can Login to the Databases.

Also interesting, the Initializer and Controls could be controlled and the Login works great. But, there is no possibility for a Local or Remote Connection to the Datebases.

Activating Romte Connection Functionality -> No Effect

Ok we tryed a lot of stuff.

Then we installed the SQL Server again in Mixed Authentication Mode. Ports in the Router are opened. And the first effect anyone can now login! But no Remote connection possible.

So we found out, that the Software Firewall in Windows Server 2k8 blocked the SQL Server. So this is easy. So now everything is working correct an we can work with it.

But 2 questions still remains:

First, what is that State 4 Thing all about. And please no internal Error Comment!

Why can just the Active Directory Administrator login?

And why have be as creative as that to get a workaround?

patric.boscolo@studentprogram.de

Microsoft Student Partners Germany

My id "sa" is going disable several times when i go to connect the database in sql server 2005

Santosh Kumar — Fri, 11 Jul 2008 08:41:37 GMT

My id "sa" is going disable several times when i go to connect the database in sql server 2005

Login failed error: (Msg 18456) SQL Server 2005 Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’ « Journey Through No-Where

Tue, 22 Jul 2008 13:16:54 GMT

PingBack from http://dotnetolympians.wordpress.com/2008/07/22/login-failed-error-msg-18456-sql-server-2005-login-failed-for-user-nt-authorityanonymous-logon/

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Sandro — Mon, 28 Jul 2008 15:59:04 GMT

I had this problem with state #8 (Password mismatch). I removed the "Enforce password policy" and now it's working.

My password was not wrong. If I pasted it in the login form on the server it would work, and if I pasted on my PC it would not. Until I uncheck this option.

Sorry but this is a bug! Lost hours for this...

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

lashai — Tue, 29 Jul 2008 19:03:04 GMT

hello,

mymyspace is blocked please help me unblock it

Log in failled message is coming....plz help....

Shyam — Thu, 31 Jul 2008 11:25:28 GMT

I'm tring to connect one server application with string "Provider=SQLNCLI;Server=195.22.3.218;Database=userdb;Uid=sa;Pwd=xyz;"

Then The folowing error message is showing....

Unable to initilize MSSQL

<mssql_error:IDispatch error #3149:Login failed for user 'sa'.>

Thanks in advance my emailid is::

smanohar@rmsindia.com

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

SQL Protocols — Sat, 02 Aug 2008 01:16:07 GMT

Shyam,

You will need to go to the server that is running SQL Server, find the server's ERRORLOG file (located at something like: %ProgramFiles%\Microsoft SQL Server\MSSQL.1\INSTANCENAME\Log\ERRORLOG), and get the State from the ERRORLOG file; then come back to this blog and compare the state with the states mentioned in the blog. As this blog post points out, there are many potential causes for the "Login failed for user 'sa'" error message, but the state will pin down the exact root cause.

Hope this helps!

Dan Benediktson

SQL Protocols

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Esa — Mon, 04 Aug 2008 13:32:49 GMT

Our application was running as a network service on a remote server (2003) and login to SQL was attempted with Domain\Server$.

Everything had worked fine for a long time, but on one day we ran into

"Error: 18456, Severity: 14, State: 11."

Finally the solution was to remove that login from SQL Server and recreate it - and everything started to work immediately.

What could be the reason for this? Has something happened in AD that had broken the login. All the time Security Event Log created success audit events for the connection but SQL Server gave the error.

Login failed for user 'PC\userf'. (Microsoft SQL Server, Error: 18456)

Stefano — Mon, 04 Aug 2008 19:41:34 GMT

hi..

i'm new using sql server.. and i have a problem to login:(

i don't know what i have to do.

i hope you can help me.

regads...

ps.. my english is bad... sorry:))

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Matt Neerincx (MSFT) — Mon, 04 Aug 2008 20:56:25 GMT

Hi Esa,

This coild happen if the SID of the login changed. When the login is added to SQL the SID of the login account it used to store the identity of the account. If the user is dropped and re-created in AD, then a new globally unique SID is generated. Hence the old SID stored in SQL's security tables is no longer valid, you need to drop and re-create the login. I'll double check with the security group to see if there is way to "refresh" the SID without having to drop and re-create the login.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Jyoti — Wed, 06 Aug 2008 12:16:41 GMT

I am getting "login failed" (Error 18456) error messages in SQL Server 2005 with no severity # and state #. I am unable to login into my college SQL server 2005 from home. Please help.

Jyoti

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Gazza — Wed, 13 Aug 2008 03:32:27 GMT

Got this state 11 error. removed the user from local admin which fixed the issue.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

MadMax — Mon, 18 Aug 2008 21:40:18 GMT

*** Look Here ****

If you are in VISTA right click on "run as administrator" and all will be well.

Geez.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

bluefang — Thu, 21 Aug 2008 05:01:19 GMT

(Login failed for user 'sa'. [CLIENT: 172.17.5.5]

Error: 18456, Severity: 14, State: 16.)

now ,I know that the meaning of state 16 is no permissions .but,i think that it's not possible about

sa. i hope you can tell me the reason.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

SQL Protocols — Mon, 25 Aug 2008 19:40:39 GMT

State 16 is a little more general than no permissions: it means that login failed while trying to log in to the database specified in the connection string. This can happen for a variety of reasons, the most common of which is that the user specified a database that they don't have permission to.

Another possibility, though, is that you've specified a database that doesn't exist: for instance, maybe you misspelled the database's name. Another possibility would be that that database is in single-user mode.

Hope this helps,

Dan

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

bluefang — Tue, 26 Aug 2008 07:25:59 GMT

Thanks!

however,this problem will appear every 5 minute.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Vakul Kumar More — Tue, 26 Aug 2008 21:12:23 GMT

MadMax said:

*** Look Here ****

If you are in VISTA right click on "run as administrator" and all will be well.

Geez.

Thanks MadMax, You saved my time...

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Sandeep — Wed, 27 Aug 2008 00:41:59 GMT

A common reason for Error is 18456, Severity: 14, State: 8. is in a development shop where the client is located in a domain and the server is not. In this case if your client is not configured to use TCP/IP you should also see corresponding errors on your Windows Server Security Audit Log - event id 529 - Unknown user name or bad password.

The solution (in SQL2005) is to configure an alias that uses TCP as the preferred protocol. To do this go to SQL2005 ...-> Configuration Tools -> SQL Server Configuration Manager -> SQL Native Client Configuration -> Alias. Lookup SQL2005 for help with setting parameters.

This should work for SQL2000 as well but the navigation path to set it up would be different.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Tanveer — Thu, 11 Sep 2008 15:18:25 GMT

hi,

My issue is exactly same which bluefang has posted earlier...

(Login failed for user 'sa'. [CLIENT: 172.17.5.5]

Error: 18456, Severity: 14, State: 16.)

Does anyone has a solution for this...

Thanks

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Shai — Tue, 16 Sep 2008 23:23:19 GMT

We're seeing an issue in our environment where we have a .NET 1.1 web application on a Windows 2003 server machine and SQL Server 2005 on a Windows 2000 server machine. In the application, we use SQL logins to connect to the db. What we are seeing is, when a bad password is used at login, an error message is generated (login failed, state 8) in the event log which is what's expected, but then we continually see the same error message repeating for the next few hours as if the connection is still alive and retrying automatically. Any ideas why? Thanks

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Red Davidson — Tue, 30 Sep 2008 05:55:40 GMT

I'm getting error 18456, severity 14, state 58. I'm trying to use SQL Server Authentication. Any info on what this might indicate.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

HariKrishna — Thu, 02 Oct 2008 11:21:46 GMT

I too receiving the same error now a days on SQL Server 2005. where we can set this sa password after installations of server.

Any help will be Appriciated.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

clydde — Fri, 03 Oct 2008 10:00:55 GMT

I have the same mistake that you:

SQL query failed .. reason [Microsoft] [ODBC SQL Driver] [SQL Server] Login failed for user 'myuser' SQL statments...

The system W.Vista Home edition.

At the moment I believe that I khan fix the problem, login me again whenever I do to consultation, in fact if I make myself it does not give mistake, but it is to solution that I do not like.

re: Understanding "login failed" (Error 18456) error messages in SQL Server Express

Tue, 14 Oct 2008 00:04:03 GMT

In a batch file, I am invoking sql script files in a loop. When each script file is run I am establishing a connection to SQLExpress instance and running script. Randomly it fails on one of the script file saying login failed. Every time it fails at different script file when i restart. Any idea, what could be the wrong?

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

tareq — Tue, 14 Oct 2008 16:08:28 GMT

Error Number: 18456

Severity: 14

State: 1

Line Number: 65536

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

SQL Protocols — Wed, 15 Oct 2008 20:44:53 GMT

k and tareq,

Can you provide the full Login Failed error message from the SQL Server ERRORLOG? That will contain a state, which as this blog post indicates is the important piece of information that tells why the login is failing. Tareq, in your case, I think that state is from the client, since State 1 is the generic we send to the client to avoid information disclosure. The server ERRORLOG file will have a different state.

SQL Server 2005 e l'errore 18456 durate il logon

DevAdmin Blog — Tue, 28 Oct 2008 00:40:38 GMT

SQL Server 2005 e l'errore 18456 durate il logon

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

kasandco — Fri, 31 Oct 2008 20:38:44 GMT

I am building some reports using SQL Server 2005 Reporting Services, using a shared data source. The particular SQL authentication I need to use connects OK in the shared data source definition, but always fails with State: 8 when I plug the shared data source name into a report's dataset definition.

I can put the credentials "locally", as it were, into a report and that's fine. Ultimately, though, I will want to schedule the reports with Report Manager so I need to get the shared data source working.

I have tried this many many times: trust me the password IS correct! Any ideas, anybody?

Logon,Unknown,Error: 18451 Severity: 14 State: 1

Adrian Balzano — Mon, 03 Nov 2008 17:46:35 GMT

This error appears on our server. But I can't find usefull documentation... Can you help me ? The server stop to respond after this.

11/03/2008 09:35:36,Logon,Unknown,Login failed for user 'XXXXX'. Only administrators may connect at this time. [CLIENT: xx.ww.yy.zz]

11/03/2008 09:35:36,Logon,Unknown,Error: 18451 Severity: 14 State: 1.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Jeremy Bond — Mon, 10 Nov 2008 21:45:39 GMT

Error: 18456, Severity: 14, State: 16.

Not Sure this has been mentioned.....

Also noticed the error can be caused by SQL Jobs that reference databases that have been removed or taken offline. We've disabled the job in question and the error has gone away. You will need to remove the job or resolve the conflicts within the job.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

sid — Fri, 21 Nov 2008 11:50:18 GMT

Why this msg has been written to the error log for specific servers only???

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

leo_learner — Tue, 25 Nov 2008 10:43:54 GMT

Hi Guys!

i am using Sql Server 2000 and getting this error in logs.

18456 :

I am very much stuck as these failures cause sql server to stuck down frequently. I dont have information like IP address of attacker like in 2005, so i have no idea what to do.

Please if any one have solution for it, please sort this problem out.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

odumah ojorma — Tue, 25 Nov 2008 14:46:20 GMT

Hope this helps.

http://benojor.spaces.live.com/blog/cns!7D1B073C4B441563!170.entry

"login failed" (Error 18456) error - state: 16 solved!

gary b — Tue, 25 Nov 2008 21:09:38 GMT

I found that I had several jobs with incorrect parameters listed. Deleting these jobs eliminated the state 16 errors that were occurring every minute.

While scrolling to end of this thread to add this information, I noticed Jeremy Bond said:

"Error: 18456, Severity: 14, State: 16.

Also noticed the error can be caused by SQL Jobs..."

Should have read ALL posts in this thread before I spent an hour tracking down bogus login.

Hope this eliminates some frustration for you.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Manoj — Fri, 05 Dec 2008 01:37:07 GMT

I am configuring Link Server for delegation between Server A and Server B. I am able to connect to both the servers from my workstation with auth_scheme 'KERBEROS'. I have created a link server on Server A for Server B with Security setting of "Be made using the login's current security context". When I run the query against the link the server I get the following error.

Error: 18456, Severity: 14, State: 11.

My login exists on both the SQL Servers as SysAdmin. All help is appreciated.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Maria — Fri, 05 Dec 2008 02:41:10 GMT

In my case:

I had Sharepoint running on a server with SQL2000. I migrated the Dbs off of that server to a machine running SQL2005. Once migrated, the new db server started spewing 18456, State 11. Specifically it said: Login failed for user DOMAIN\Sharepointserver$. As this was not an AD user, but a Computer, I didnt really get it.

But, to resolve the problem, I went to the SQL 2005 server and created a new login under Security as DOMAIN\Sharepointserver$ (made sure to match spelling/case perfectly.) I gave that login DBCreator rights, and in User Mapping I checked the boxes for the sharepoint dbs, and then verified it had Permission to Connect to Database Engine enabled, and hit OK.

Error 18456 stopped. Didnt have to restart any services on either box.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

manojmsingh — Fri, 05 Dec 2008 17:56:27 GMT

My issue is that I can't even create a link server successfully it throws the error

In my first post I may have hinted that the link server was created successfully but I can't do that. so for some reason it is not doing the security account delegation even though I am able to login to the server A and server B with 'KERBEROS'.

The setting on the computer object in AD has also been set to "Trust for delegation".

Any other setting that I may be missing.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Matt Neerincx (MSFT — Fri, 05 Dec 2008 21:02:06 GMT

Ensure when you create the linked server that you look very very carefully at the Security section and select the option that is right for you.

The SIMPLEST solution is to select "Be made using this security context" then supply a NT user name and password to use. This avoids all Kerberos double hop issues as the linked server runs under an account you control and the account logs in at the server (versus passed from client to server).

Likewise you can assign mappings for users here as well, this is another solution to avoid Kerberos double hop issues.

If you truely want the incoming client's account to be used with the linked server connection, then select "Be made using the login's current security context". This option REQUIRES Kerberos double hop to be enabled.

To enable Kerberos double hop you need to ensure:

1. Account that SQL Server service is running under has rights to delegate accounts in AD.

2. Each user account coming from client has "allows delegation" set in AD.

3. Each client can fully resolve the FQDN of the SQL Server (DNS is setup correctly, they are not connecting via IP address for example).

4. A SPN (Server Principle Name) is registered for the SQL Server service account.

Setting up Kerberos is not for the faint of heart unfortunately.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

manojmsingh — Fri, 05 Dec 2008 21:45:20 GMT

thanks for the information and I don't think I am faint of heart.

#1 this I was told is only for the computer and not the service account.

#2 "Account is sensitive and cannot be delegated" is not selected for the account I am using to run query across link server

#3 my workstation can fully resolve the FQDN of the sql server. Verified by running ping -a <servername>

$4 Done and verified by running setspn -L

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

manojmsingh — Fri, 05 Dec 2008 21:55:20 GMT

Also we do need security account delegation over the link server, this is for BizTalk setup. Secondly for #1 Account is sensitive and cannot be delegated is not selected for the service account.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

tfudge — Tue, 09 Dec 2008 21:53:48 GMT

2008-12-09 12:46:05.59 Logon Error: 18456, Severity: 14, State: 16.

2008-12-09 12:46:05.59 Logon Login failed for user 'NEWMILLSCADA\Robert Hardin'. [CLIENT: 192.168.1.100]

2008-12-09 12:47:15.59 Logon Error: 18456, Severity: 14, State: 16.

2008-12-09 12:47:15.59 Logon Login failed for user 'NEWMILLSCADA\Robert Hardin'. [CLIENT: 192.168.1.100]

2008-12-09 12:48:25.60 Logon Error: 18456, Severity: 14, State: 16.

I get these eroors every ten seconds. I am not a databse expert, but a control systems engineer and have no idea how to fix this. Any help would be appreciated.

Thank You,

Tim

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

R.Hoek — Thu, 11 Dec 2008 18:30:46 GMT

Hello,

I'm also getting State 16 errors on a SQL user, which has permissions to access the target database. And we're also using IIS (from an other server) to connect to the SQL server.

As far as i've read correctly, this error can also occour when the database of (temporarely) offline / not available.

But we have not taken the DB offline, so SQL server has some trouble accessing the DB itself.

Is there any way to determine why the DB is offline/not accesable (logging?) other then using the USE DATABASE statement?

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Chris — Fri, 12 Dec 2008 02:56:54 GMT

we had the Error 18456. We followed the steps that described in the document and resovled the problem:

http://www.fulltimedba.com/2008/12/11/Error18456Severity14State11.aspx

Troubleshooting login problems

Tibor Karaszi — Wed, 17 Dec 2008 14:41:51 GMT

I've been learning a few things about troubelshooting login problems over the last few months. I first

Troubleshooting login problems

SQL Server — Wed, 17 Dec 2008 14:56:18 GMT

I've been learning a few things about troubelshooting login problems over the last few months. I first

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

greg aiken — Wed, 17 Dec 2008 19:27:36 GMT

sql2005 error log lists...

error 18456

severity 14

state 16

i read above in this thread two possible reasons, but i am offering a 3rd. when i ran ssmsee and saw the database in the left pane. the database icon was not the normal icon. instead of seeing the normal icon, i saw an icon of a persons head and shoulders in front of the database. and next to the database were the words 'single user'. somehow the database took on the property of 'single user'. once this happened, none of the sql authenticated logins (which previously worked), could be used to log into the database. fortunately detaching, and reattaching the db, set things right again.

but my question is... what does it mean when it says the db is in 'single user' mode, and more importantly WHAT COULD HAVE CAUSED THIS? the mode change basically left an office unable to get any work done.

any help here would be appreciated...

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

SQL Protocols — Thu, 18 Dec 2008 04:13:07 GMT

Greg,

You probably have other application or service(e.g. SQL Agent) running to turn the database into "single user" mode. You can use SQL Server Profiler to find out who did it. Please refer to BOL

http://msdn.microsoft.com/en-us/library/ms345598.aspx

Thanks.

Xinwei

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Delson — Mon, 05 Jan 2009 10:35:57 GMT

the sql server loging mode is set to windows authentication set it to sql authentication , to do this right click on the server in sql server managment> properties and then change the loging mode.

Login Probleme nach Update auf SQL Server 2005 | hilpers

PingBack from http://www.hilpers.com/350156-login-probleme-nach-update-auf

accesso con autenticazione SQL non riuscito (18456) | hilpers

accesso con autenticazione SQL non riuscito (18456) | hilpers — Wed, 21 Jan 2009 20:26:56 GMT

PingBack from http://www.hilpers.it/2537843-accesso-con-autenticazione-sql-non

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Faran — Fri, 23 Jan 2009 00:26:10 GMT

If you got state 1 log_in failed message, you need to add some SQL privileges to the account you've logged in with.

Go to SQLServerManagementStudio

Configuration Tools

SQL Server Surface Area Configuration

Add new Administrator

Add the two privileges on the left to your account (which you are logged in with)

follow up to 12-17-08 11:27am post (single user) mode & 'sql profiler'

greg aiken — Tue, 27 Jan 2009 01:03:39 GMT

dear Xinwei,

the problem posted on 12-17 continues to happen with regularity. your information says to use 'sql profiler' to figure out who, or what process, is mysteriously changing the database status to 'single user' mode.

question 1. is 'sql profiler' available for sql 2005 express? if so, where, or how does one find, or install it?

question 2. if 'sql profiler' is only available with sql 2005 standard (or higher). how would one setup sql profiler for sql 2005 standard?

question 3. in speed reading books online about 'sql profiler' it seems that one must know what events or sql commands to trace. can you suggest how one would set the correct sql to look for that might set the database mode to single user mode? ive seen there is a sql command that explicitly sets the database mode to single user, but ive also read that some built-in functions such as 'dbcc' might also set the database mode to single user. who knows, perhaps there are many such functions... how would you suggest i try to configure 'sql profiler' to detect what is changing the database mode to 'single user'?

thanks alot

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

felipe — Fri, 30 Jan 2009 20:39:19 GMT

hi, i am getting a sql error with state 5.

how do i solve this?

thanks!

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Daniel Macey — Wed, 04 Feb 2009 01:42:35 GMT

I have also encountered a couple of new States in SQL Server 2008:

* Login failed for user '<username>'. Reason: Failed to open the explicitly specified database. [CLIENT: <IP>]

Error: 18456, Severity: 14, State: 38.

This error occurs when the database specified as the initial catalog is unavailable (for security or otherwise). This was reported as a State 16 in SQL Server 2005.

* Login failed for user '<username>'. Reason: Failed to open the database specified in the login properties. [CLIENT: <IP>]

Error: 18456, Severity: 14, State: 40.

This error occurs when the users default database is unavailable. This was reported as State 16 in SQL Server 2005.

Hope this helps someone else.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Niklas Klingspetz — Thu, 05 Feb 2009 12:36:49 GMT

After installing SP3 (KB955706) for SQL Server 2005 i got an:

Error: 18456, Severity: 14, State: 11.

in sqlservers logfiles. Eventlog just says

I used Windows Authentication in the 2005 Management Studio, the connection was working just fine before SP3.

SOLUTION: Use "run as administrator" when starting Microsoft SQL Management Studio 2005.

ENVIRONMENT: Vista Business - all recommended updates for SQL Server 2005 and Vista as of 2009-02-04 installed.

re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

sandra — Thu, 12 Feb 2009 16:48:26 GMT

what about state:0 ?

I have this error and I dont know what happens:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: TCP Provider, error: 0 - Se ha forzado la interrupción de una conexión existente por el host remoto.) (.Net SqlClient Data Provider)

------------------------------

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=10054&LinkId=20476

------------------------------

Server Name: DESSQL2008

Error Number: 10054

Severity: 20

State: 0

------------------------------

Program Location:

at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)

at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj)

at System.Data.SqlClient.TdsParserStateObject.ReadPacket(Int32 bytesExpected)

at System.Data.SqlClient.TdsParserStateObject.ReadBuffer()

at System.Data.SqlClient.TdsParserStateObject.ReadByte()