Environment: SQL Server Express 2008 R2, EW4, Windows XP SP3
Here are a few important program statements from the ASPX page.
conn=Server.CreateObject("ADODB.Connection")
conn.Open (connString) --> conn.State = True
sql1 = USE [my_db]; SELECT * FROM [dbo].[CustomerTable];
rs=Server.CreateObject("ADODB.Recordset") --> rs(create).State = False
rs.Open (sql1,conn) --> rs(open).State = False
conn.State = True
do until (rs.EOF) // produces fatal exception
From my log file (C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Log\ERRORLOG):
2017-01-02 12:31:06.33 Server The SQL Server 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.
These are from Procmon.exe (SysInternalsSuite)
Date & Time: 1/2/2017 12:21:33 PM Event Class: File System Operation: DeviceIoControl Result: INVALID PARAMETER Path: C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\ MSSQL\DATA\my_db.mdf TID: 4972 Duration: 0.0000050 Control: IOCTL_MOUNTDEV_QUERY_DEVICE_NAME
Date & Time: 1/2/2017 12:21:33 PM Event Class: File System Operation: CreateFile Result: NAME INVALID Path: C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\ MSSQL\DATA\my_db.mdf TID: 4972 Duration: 0.0000107 Desired Access: Read Attributes, Synchronize Disposition: Open Options: Synchronous IO Non-Alert, Open Reparse Point Attributes: N ShareMode: Read, Write AllocationSize: n/a Impersonating: servername\mainuser
Regarding earlier efforts about resolving the SPN issue mentioned in the SQL Server ERRORLOG, I found the following reference:
https://msdn.microsoft.com/en-us/library/ms191153(v=sql.110).aspx#Defaults
So Kerberos is used for remote connections. There are only local connections in this situation. So Kerberos is not used locally and probably does not matter in this case. NTLM is used instead locally.
So the non-registration of SPN is probably a non-issue in this case.
For a time the unable to register SPN and Kerberos authentication warning mentioned in the ERRORLOG seemed to be the only item that could be followed up on. It was the only significant error (warning) listed there and for a time seemed the only lead.
Initially the SPN issue seemed to be caused by some inadequate permission setting. Moreover many web pages seemed to be attributing both the SPN issue and the inability to get the queries to run to inadequate permission settings. This user began to think inadequate permissions were the root cause of his imagined recordset create object fail.
Specifically, for a time, this user thought the sql server startup account was unable to create the recordset object because it did not have enough permission(s) to accomplish the task. Moreover MSDN web articles suggesting to grant Read and WriteServicePrincipalName rights using Active Directory (to enable the Kerberos authentication for remote connections) furthered this users mistaken understanding. Ultimately, this line of thought was proven incorrect, since the computer was a standalone (not networked), and not being a server os could not have an AD DC (locally), did not have any remote db connections and was not needing Kerberos authentication for remote connections.
So the SPN and Kerberos warning in the sql server ERRORLOG was finally judged to be a non-issue in this situation. But this part of the investigation consumed much time and effort, very confusing.
Although, regarding permissions, setting (changing) the sql server startup account logon type from local service to local system (a higher privileged account) did seem to improve things.
Please accept my appreciation for the help and effort you (and others) have put into my program. I suspect that you likely have more sql server experience than I may ever acquire, consequently deserving respect. But my goal is different, to get the website fully up and running. I am carefully trying to follow your advices.
In general, regards this program, it is important because until fixed there is no useable database, no useable website, and engineering company cannot open its doors.
Regards this program, I heavily suspected there was really only one (major) persistent problem (later revealed to be the malformed query string). Initially it looked like a db connection or permission problem was causing the create recordset object to fail (rs.State=0), but this turned out to be incorrect. The db connection proved adequate and the rs.State=0 (for the create rs object) was not an error, more like a born with zero records message. The initial rs.State=0 (for the rs create object) was misunderstood and proved to be very confusing.
Even more confusing, rs.State actually returns an enumerated value (essentially an integer) not a boolean value (such as the False shown in the code snippet view window). An unintended data type mismatch is shown there (unintended illegal cast). So the original code snippet view window was not perfectly correct in that way.
When the program finally ran correctly rs(create).State=0 and rs(open).State=1, which was not the expected result. Indeed the initial expectation that rs(create).State and rs(open).State should both equal 1, proved incorrect. That this user carefully watched the rs(create).State method object property value proved to be a useless occupation. (It did not really help.)
When the "USE [my_db]; " portion was removed from the sql query string, then the database began to be correctly queried and RS.State=1 (for the rs.Open). Up to that point the "USE [my_db]; " killed every query. Things began to clear up and it then became apparent there was (or had been) a triple database connection specification which is usually (or always) harmful. Two in the connection string, with the AttachDBFilename parameter and the Database parameter. The third in the sql query string with the "USE [my_db]; ". The three db connections undoubtedly caused some software problem (since over specified) even though all three entries were same and correct (to the human). Further testing with Procmon.exe (SysInternalsSuite) showed errors associated with the AttachDBFilename parameter and zero errors with the (connection string) Database parameter when used alone. So the Database parameter was chosen for use alone in the connection string.
Regarding the Multiple Active Result Sets (MARS) attribute connection string parameter, the following MSDN web article says it is initially disabled by default. Apparently it is an advanced db feature which involves several advanced issues, sychronization, asynchronization, caching, multiple batched command sessions. While the MARS feature is valuable, it is thought it should be left disabled until the user becomes advanced enough to handle it correctly in code. So this MARS connection parameter is not recommended for db beginners. https://msdn.microsoft.com/en-us/library/h32h3abf(v=vs.110).aspx
Often used with MARS, the "DataTypeCompatibility=80; " parameter restricts db data types to the 2005 set. Which seems unnecessary and disadvantageous. This parameter is not recommended unless using MARS. https://msdn.microsoft.com/en-us/library/ms131002(v=sql.110).aspx
Finally an Equivalent key-value pair: "Integrated Security=SSPI" equals "Trusted_Connection=yes".
So chose one to avoid redundancy.
One comment said: "Take a look here for info on how to create the correct connection string: https://social.technet.microsoft.com/wiki/contents/articles/1409.how-to-create-a-sql-connection-string-for-an-application-udl-file.aspx ." I do feel this UDL procedure is useful because it provides system confirmation what the correct provider (and some other things) should really be. Even so I finally used a custom connection string.
Regarding the confirmation connection string, the exact result was:
[oledb]
; Everything after this line is an OLE DB initstring
Provider=SQLNCLI10.1;Integrated Security=SSPI;Persist Security Info=False;User ID="";Initial Catalog=my_db;Data Source=serverName\SQLEXPRESS;Initial File Name="";Server SPN=""
I would especially like to thank Nick. He expended great effort and stayed with me until this reached successful resolution. His comment about removing "USE [my_db]; " led directly to the fix. Having done that the simplest test sql query then actually ran correctly. And all the other errors fell away like a house of cards (a good thing). So I guess he found the root of my problem. Sincerest thanks to Nick.
This is part theory and may not be completely correct but this is my understanding.
It appears that when Recordset.Open returns no records, it returns with a state=0
When you run this query:
USE [my_db]; SELECT * FROM [dbo].[CustomerTable];
It returns the first recordset from the first statement. In this case the first statement is
USE [my_db];
which returns no records
So no errors would be thrown in the open part because it has run perfectly
Errors will be thrown when you check rs.EOF because the recordset is closed (because the statement didn't return any records or columns)
If you look under the covers of the fatal exception you would see an error something like that.
I suspect that if you selected from an empty table the result would be a bit different, i.e. an open recordset with no records.
This is very similiar to the SET NOCOUNT ON requirement when writing stored procedures