Unable to Delete Records in using API Cursors
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:
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=180150013DECLARE @p3 INT
SET @p3=1DECLARE @p4 INT
SET @p4=16386DECLARE @p5 INT
SET @p5=1EXEC sp_cursoropen @p1 output,N'SELECT * FROM Table WHERE ID=''1223'' AND SiteID = ''ZZZZ''',@p3 output,@p4 output,@p5 outputSELECT @p1, @p3, @p4, @p5GO
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:
- SQL Learnings, Error 14274. Link.
- SQL Learnings, Error 18456. Link.
- Journey to SQL Authority with Pinal Dave, Error 7411. Link.
- API Cursors. System Stored Procedures. Link.
- SQL Server Performance. Exposing API Server Cursors. Link.
- MSDN. sp_addserver. Link.
- MSDN. sp_serveroption. Link.
