Advertisements

Archive

Posts Tagged ‘SQL Error 18456’

Unable to Delete Records in using API Cursors

March 11, 2010 1 comment

I am not very familer with the internals of API Cursors; so this was an annoying problem to solve.  I was working with web guy, he had a user working on Web Front-End application.  When user tried to delete record from the database they got following error:

Microsoft OLE DB Provider for SQL Server error ‘80004005’ 
Login failed for user ‘Domain\ServiceAccount’.

I thought it was a typical error so I my first thought was it will be easy problem.  Looking at SQL logs I had following error logged for this error:

Login failed for user ‘Domain\ServiceAccount’. [Client: 10.0.0.1]
Error: 18456, Severity: 14, State: 8

State 8, means that the user login in is not a SQL Login account, but is trying to login in like one.  With this information in hand I talked to web guy and asked him to confirm his application pool credentials and connection string for me.  He confirmed for me the application pool and connection string are not using the ‘Domain\ServiceAccount’; so we were confused as to where it was getting this account from.  The IP listed in second error message gave us an idea where to look.  It was the SQL Servers’ IP address; but that confused me again. Why did we have transaction activity from web server switched to SQL Server?  I started SQL Server Profiler and logged the following:

DECLARE @p1 INT
SET @p1=180150013 
DECLARE @p3 INT
SET @p3=1
DECLARE @p4 INT
SET @p4=16386 
DECLARE @p5 INT
SET @p5=1 
EXEC sp_cursoropen @p1 output,N'SELECT * FROM Table WHERE ID=''1223'' AND SiteID = ''ZZZZ''',@p3 output,@p4 output,@p5 output
SELECT @p1, @p3, @p4, @p5 
GO
EXEC sp_cursorfetch 180150013,16,1,1
GO
EXEC sp_cursorfetch 180150013,16,2,1
GO
EXEC sp_cursorfetch 180150013,1040,1,1
GO
EXEC sp_cursor 180150013,34,1
GO
Login failed FOR USER 'Domain\ServiceAccount'. [CLIENT: 10.0.0.1]
EXEC sp_cursorclose 180150013
GO

Notice again same failure is noted in middle of API cursor calls; “EXEC sp_cursor ….,34,1” statement means to delete the current record.  What was most confusing was why was SQL Server it self trying to login using another service account when delete got executed?

Digging into the configuration a bit; I found someone had created a Linked Server to the local server.  If you try to create Linked Server under (Server Objects -> Linked Servers) to local server you get an error message “You cannot create a local SQL Server as a linked server“.  So how did this get added using the ‘Domain\ServiceAccount’ account?

Looking at it a bit more it is possible to add a linked server to local server (its useless to do, as far as I can tell) by using sp_addserver stored procedure and then using sp_serveroption stored procedure you can set properties.  But the GUI interface will not let you interact with the object.

Now I had answer to why SQL Server was trying to login to it self using that service account; it was mystery to me why was the API Cursors were using the linked server.  I confirmed the SQL code being from front end (above) did not have four-part name for table object (Server.Database.Schema.Table).  There should be no reason for it to fall back on linked server, as this was not a distributed query coming from another SQL Server either.  I checked sys.server, it had one entry in it with id value of 1 but nothing for id value of 0 (default value).  So I decided to delete the linked server (id = 1 in sys.servers) and got web guy to try again; now error changed to following:

Microsoft OLE DB Provider for SQL Server error ‘80004005’
Could not find server ‘SQLServer’ in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

I started Googling to see if I can find the issue; with not much luck.  But while reading Pinal Dave’s blog I got idea to check @@ServerName property; which returned ‘NULL’.  Again running into further confusion I decided to do listing on sys.servers to see what entries exist in there as per the error messages suggestion.  It returned 0 rows (as I deleted the only row visible); but there should be at least one row in this table referencing the local server with id value of 0.  This server was not renamed, it was always the same name; I don’t know why this entry was missing.  My guess was someone added the missing entry using “sp_addserver ‘SQLServer’, ‘SQLServer'” which created a remote server connection instead of local server connection.  I executed following commands to adjust entries in this table:

EXEC sp_addserver 'SQLServer', 'local' 
GO

This added a new entry called id = 0 (which what the default entry should be).  So since the local server was no longer viewed as a linked server under server objects I got web guy to try again, the error changed to following:

Microsoft OLE DB Provider for SQL Server error ‘80004005’
Server ‘
SQLServer‘ is not configured for DATA ACCESS.

Now reading Pinal Dave’s blog some people suggested enabling RPC Out property; now this is valid for a linked server.  But this was not linked server so why did I have this error?  Looking at @@ServerName, it was still ‘NULL’; even with record id of 0 in sys.servers.  This is because global variables like that are populated at boot time, so I had to restart SQL Server services before that variable was populated.

After @@ServerName returned proper value; the web service started running also.  I’ll be investigating the link between API cursors and @@ServerName in another article; for now problem solved.

Problem: Unable to do delete operations in an application that relies on API Cursors.
Solution: Check to make sure the @@ServerName property is set; if not check sys.servers.  If there are no rows in the table.  Add new entry using sp_addserver and restart SQL Services.

References:

  1. SQL Learnings, Error 14274. Link.
  2. SQL Learnings, Error 18456. Link.
  3. Journey to SQL Authority with Pinal Dave, Error 7411. Link.
  4. API Cursors. System Stored Procedures. Link.
  5. SQL Server Performance. Exposing API Server Cursors. Link.
  6. MSDN. sp_addserver. Link.
  7. MSDN. sp_serveroption. Link.

Error 18456, Severity 14: Login Failed States

October 3, 2008 Leave a comment

The document referenced below explains all the states, this is just a summary.

State Description
1
Only state returned in SQL 2000. Seems to be catch-all where no other states apply; for example Account Disabled shows up as State 1(2005).
2
Invalid Userid
5
SQL Account provided does not exist on SQL Server. (SQL2005)
6
Attempt to use a windows login name with SQL Authentication
7
Login disabled and password mismatch
8
Password mismatch
9
Invalid Password (i.e. did not meet minimum password criteria)
10
Read Ref Link #2
11
Windows account does not exist on SQL Server or CONNECT permissions is set to DENY for the login. (SQL2005)
12
Valid login but server access failure; CONNECT permissions is set to DENY for the login.
13
SQL Server service paused
14
????
16
User doesn’t have permission to target (connection string) or default database (SQL Server login setting) (SQL 2005)
18
Change password required
23
Server in process of shutting down, and user attempted to login.
27
Initial database could not be determined for session
38
Initial database could not be determined for session (SQL 2008)
40
Default database could not be accessed (SQL 2008)
58
SQL Server is set to Windows Only Authentication, SQL Login not allowed.(SQL “Denali”)

In SQL 2005 it reports as state 1, user is not associated with a trusted account.

Ref Link #1: States Explained, http://blogs.msdn.com/sql_protocols/archive/2006/02/21/536201.aspx
Ref Link #2: KB925744, Issue with State 10, http://support.microsoft.com/?id=925744
Ref Link #3: Troubleshooting: Login Failed for User ‘x’, http://msdn.microsoft.com/en-us/library/ms366351.aspx

Updated: March 6th, added state 38/40 for SQL 2008.
Updated: March 12th, added a bit more detail about state 11/12 and added state 1.
Updated: April 14, 2011, added state 58. Updated state 5, 11, & 12. If I added (Version); it means I have confirmed/found it on that version.
Updated: November 22, 2011, updated the state 8, what is invalid password?

%d bloggers like this: