Jump to content

ASPX [ASP.NET] rs=Server.CreateObject() fails


kennr

Recommended Posts

The root problem is an ASPX (ASP.NET) web page program, part of an important business program presently in development and connected to an sql server database (.mdf) file, is presently unable to do two procedures: rs=Server.CreateObject("ADODB.Recordset") and

rs.Open (sql1,conn). Both operations return rs.State = False. The reason is not clearly understood. The system is Windows XP SP3.

The only error observed in the MSSQL ERRORLOG is:

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.

When researched on Internet this first url was found:

Reference(1): https://blogs.technet.microsoft.com/mdegre/2009/11/08/the-sql-network-interface-library-was-unable-to-register-spn/

Where it says:
If you run SQL Server under the LocalSystem account, the SPN is automatically registered as SQL registering with the machine
account that has the right to create an SPN default. So Kerberos interacts successfully with the server running SQL Server.

The settings currently in use are SQLEXPRESS (2008R2) service, on Manual, with Local System startup account logon. This startup
account is believed to have enough permissions to register the SPN, but that may be for an sql expert to decide. The SQL Server is
started manually using the System Tray Icon, when needed.

So upon further thought, the inadequate permission argument for causing the failed Recordset object creation does not seem to make
complete sense. Since the Local System account being used for the sql server startup account should already have enough permission?
On the other hand, the SQL ERRORLOG continues to say it could not register the SPN? (So Internet research was continued.)

Then on Internet this second url was found:

Reference(2): https://blogs.msdn.microsoft.com/dataaccesstechnologies/2010/01/06/how-to-grant-readserviceprincipalname-and-writeserviceprincipalname-rights-to-sql-server-service-start-up-account-without-using-adsdiedit-tool/

Comment: Ironically (considering the url article title) the decision was to use the ADSI Edit tool to assign greater permissions.

Where it says:
Instead of setting SPNs manually, you may want to give ReadServicePrincipalName and WriteServicePrincipalName rights to
SQL Server service start-up account so that it can register and de-register SQL Server SPNs on its own whenever the SQL Server
service is started and stopped. As the above articles describe, these rights can be granted from ADSIEDIT tool.

Comment: The intended use of ADSI Edit tool seemed close to succeeding and might have succeeded but all attempts to connect ADSI Edit to the running SQL Server local instance have only been unsuccessful. ADSI Edit seems unable to see the running local sql server. Not enough was known about ADSI Edit to fix that. This is a standalone laptop without a domain and without a LAN. (But again there is the local sql server instance to connect to.) Then two SQL stored procedures sp_ActiveDirectory_SCP and sp_ActiveDirectory_Obj fail while attempting to register the SQL Server instance and the database (.mdf) file into the Active Directory database saying instead the system does not have Active Directory installed (but of course it does, at least partially).

Then on Internet this third url was found:

Reference(3): https://technet.microsoft.com/en-us/library/cc773354(v=ws.10).aspx

Where it says:
Adsiedit.msc automatically attempts to load the current domain to which the user is logged on. If the computer is installed in a
workgroup or otherwise not logged on to a domain, the message "The specified domain does not exist" displays repeatedly. To resolve
this issue, you may want to open an MMC, add the ADSI Edit snap-in, make connections as appropriate, and then save the console file.

The ADSI Edit snap-in was succesfully added to MMC. But ADSI Edit still seems unable to make the connection in this situation. And the console file would not save.

There are two command line tools (from win supp tools 2003) which can also set these R/W rights. One being setspn.exe and the other dsacls.exe. But they both also seem to read and store the permissions information in the Active Directory database (NTDS.dit) file. So Iwould not expect them to work either for similar reasons.

It seems I am trying to get Active Directory working in a windows workgroup consisting of only one machine. Is that, by definition, impossible, since a workgroup needs to have a minimum of two machines?

Reference(4): https://mssqlwiki.com/2013/12/09/sql-server-connectivity-kerberos-authentication-and-sql-server-spn-service-principal-name-for-sql-server/

The reference(4) does not change my situation because it still requires an Active Directory database which again requires a domain or workgroup (LAN) neither of which are present in my case. But reference(4) is still beneficial because it excels at providing such a detailed Active Directory configuration example.

My last thought is that in the absence of a domain or workgroup (LAN), there are still present in my case the SQL Server instance and the localhost server (provided by MS Expression Development Server). So I came to wonder if it was possible for ADSI Edit to connect to those two servers somehow? (Attempts so far have been unsuccessful.)

I cannot imagine how my situation is so unusual. Is mine the only machine running ms sql server in a standalone configuration? I would expect there to be (many) others.

Thank you for your time.

Please advise.

Edited by kennr
Link to comment
Share on other sites

I'm not sure what you mean by this:

fail while attempting to register the SQL Server instance and the database (.mdf) file into the Active Directory database saying instead the system does not have Active Directory installed (but of course it does, at least partially).

But why not just set it up so that you're using a domain instead of a workgroup?
Link to comment
Share on other sites

Hello. As I explained above, this is an important programming project. So I am very appreciative of any help you and others can provide me. Please accept my appreciation.

 

My laptop is presently standalone, not part of a workgroup (LAN) and not part of a domain either. That situation (for the laptop) may not change any time soon. Although in the near future, when the web pages are FTP (published) up to its commercial web host site, then the website will then be on its own domain. Just not yet during (local) development.

 

Are you suggesting I setup my laptop into a domain or workgroup (LAN) configuration so the ADSI Edit network tools can work? That implies making a domain or workgroup (LAN) consisting of just the one computer only. Is that possible? If so, how done?

 

I am left considering whether there could be yet a local solution (without a workgroup or domain). I keep wondering, why was the local system (sql server startup account) not already having enough permissions? I thought it was supposed to.

 

This is important and I do not understand.

 

Thank you.

Link to comment
Share on other sites

Yes, I'm suggesting setting up your own domain or whatever else you need to do to make sure your laptop is a good development environment for what you're trying to write. If you're trying to use tools that rely on things like Active Directory, then you need to set up Active Directory.

 

Note that Windows domains are not the same thing as web domains, just because you host your files on a server that has a domain name pointed to it doesn't mean it is part of a Windows domain, they aren't the same thing.

 

If you want to set up a domain then you need to install Active Directory and set up your computer to be the domain controller. Obviously your computer is going to do several roles, domain controller, web server, database server, etc. That's just part of setting up your development environment. I don't know if you can set everything up without doing all of that, I haven't researched everything you're trying to use, it might be possible, but if you're using tools that rely on things like Active Directory then you need those things installed and configured.

Link to comment
Share on other sites

Hello. Thank you for waiting until my reply.

The good news is the Active Directory tools are installed, ready to use. And the buttons at My_Computer>System_Properties>Computer_Name are also enabled ready to use.

The not so good news is the windows domain and workgroup are not yet setup correctly.

Errors were returned there. Request help with this.

------------------------------------------------------------------------------------------------------------------------------------
My_Computer>System_Properties>Computer_Name says (buttons are enabled):

To use the Network Identification Wizard to join a domain and create
a local user account, click Network ID. <Network ID>
(but the second page for option 2 Ref#1 was not observed)

To rename this computer or join a domain, click Change. <Change...>
(but produced errors listed below, Ref#2 and Ref#3 might help)

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

The Computer Name Changes module then reports these errors:

A domain controller for the <specified> domain could not be contacted.
The following error occurred when DNS was queried for the service
location (SRV) resource record used to locate a domain controller for
the <specified> domain:

The error was: "DNS name does not exist."
(error code 0x0000232B RCODE_NAME_ERROR)

The query was for the SRV record for
_ldap._tcp.dc._msdcs.<specified domain>.com

Common causes of this error include the following:

- The DNS SRV record is not registered in DNS.

- One or more of the following zones do not include delegation to its
child zone:

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

Reference#1: https://support.microsoft.com/en-us/kb/295017

Reference#2: http://geekswithblogs.net/technetbytes/archive/2011/10/09/147233.aspx

Reference#3: http://serverfault.com/questions/76715/windows-active-directory-naming-best-practices

Edited by kennr
Link to comment
Share on other sites

It sounds like Active Directory isn't set up as the domain controller for that domain. For what it's worth, when I set up AD domains I don't use a .com or other TLD, I just use the company name or some other abbreviation. The domain in our office network is just called TC, for example, and the domain controller is set up to act as the authority for that domain.

Link to comment
Share on other sites

Hello.

I thought our intention is to Install Active Directory and DNS on this Windows XP (client) machine. But I am now concerned whether this is possible (since mine is only a network client OS and not a Server OS)?

It seems likely this Win XP client can now connect to an existing Windows domain (if there were one). But it also seems unlikely this Win XP client can now serve as its own domain controller. Do you agree?

In your reply, you mentioned "when I set up AD domains". Have you ever setup a domain controller on a client only machine? From your experience, what are the AD DC client/Server installation differences?

 

I have wondered why the local system (sql server startup account) does not have enough permissions to do the rs=Server.CreateObject("ADODB.Recordset"). An sql server expert could comment whether the permissions on the local system account were correctly set in my case. Setting permissions another way could circumvent the AD DC problem.

 

Please do not be frustrated with me. This project is so very important. Without a successful resolution, the website has no database, the company has no website, no jobs. Not good.

Please help if you can. I do not know who else to ask. Is there a Windows network expert on your team? Perhaps he/she could help.

Thank you, (sincerely).

---------------------------------------------------------------------------------------------------------------------------------------------------------------------
I expected Reference#1 to help with Install Active Directory and DNS, but that msdn help page only pointed out that my machine needs to be running a Server OS (of one type or another) and setup as a DC (domain controller).

I then expected Reference#2 to help with Configuring a Domain Controller, but it also appears to apply to a running Server OS (of one type or another) and setup as a DC (domain controller).

Last, I expected Reference#3 to help with Configure Windows Active Directory and Domain Controller, but it also appears to apply to a running Server OS (of one type or another) and setup as a DC (domain controller).

Still there is some good news.

Reference#4 shows how to register the new Windows domain in the \WINDOWS\system32\drivers\etc\hosts file.

From Reference#4:

Verify that the /etc/hosts file on the DC correctly resolves the fully-qualified domain name (FQDN) and short host name to the LAN IP address of the DC. For example:

127.0.0.1 localhost.localdomain localhost
10.99.0.1 DC1.samdom.example.com DC1

---------------------------------------------------------------------------------------------------------------------------------------------------------------------
Reference#1: https://msdn.microsoft.com/en-us/library/ms942145(v=cs.70).aspx

Reference#2: https://msdn.microsoft.com/en-us/library/ms943015(v=cs.70).aspx

Reference#3: https://docs.oracle.com/cd/E19575-01/820-3746/gisdn/index.html

Reference#4: https://wiki.samba.org/index.php/Setting_up_Samba_as_an_Active_Directory_Domain_Controller#Preparing_the_Installation
---------------------------------------------------------------------------------------------------------------------------------------------------------------------

Edited by kennr
Link to comment
Share on other sites

It's been a long time since I've used Windows XP, but from what I can find online it looks like it does not have the ability to act as a primary domain controller.

 

Have you ever setup a domain controller on a client only machine? From your experience, what are the AD DC client/Server installation differences?

I've never used any version of Windows XP as a domain controller, I've used Windows 2000, Windows Server 2003, and Windows Server 2012. There are several differences that make Windows Server different than Windows XP but I don't know what all of them are.

 

You might want to ask at a specialized forum like this one:

 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/home?category=sqlserver

 

This forum is really just for general web programming, we don't get a lot of sysadmin questions here. That link to the Samba documentation is for a package that runs on Linux, I don't think that's going to help you much. The hosts file in general is just a way to override any other DNS lookups, the OS will first look for the domain in the hosts file before trying to resolve it through DNS.

Link to comment
Share on other sites

Hello.

 

I have been doing some work on my SQL Server db connection string. This is because (importantly) Ref#1 (near bottom part) indicates that a faulty conn string will cause a closed recordset object (same as my root problem).

Apparently in that particular post, his conn string was good enough to connect to his db. But the provider attribute (or driver) he originally used could not correctly read the db records. So the return recordset object was then automatically closed.

The Ref#2 just shows how to properly construct the conn string. And Ref#3 is simply a reference for all possible conn string attributes.

So having reconstructed my conn string, I get the following. Which connects speedily and reliably, but still results a closed recordset object.

Can my problem still be as simple as a faulty conn string?

connString = "Provider=SQLNCLI10;" _
& "Server=serverName\instanceName;" _
& "AttachDbFilename = C:\Program Files\Microsoft SQL Server\" _
& "MSSQL10_50.SQLEXPRESS\MSSQL\DATA\my_db.mdf; " _
& "Database=my_db;" _
& "Trusted_Connection=True;" _
& "Integrated Security=SSPI;" _
& "DataTypeCompatibility=80;" _
& "MARS Connection=True;"

Please carefully check this conn string for me and/or have a sql server expert do same.

Thank you.

 

Reference#1: http://stackoverflow.com/questions/31854468/error-operation-is-not-allowed-when-the-object-is-closed

Reference#2: https://msdn.microsoft.com/en-us/library/ms130978.aspx

Reference#3: https://www.connectionstrings.com/sql-server-2008/

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

 

Regarding earlier efforts about resolving the SPN issue mentioned in the SQL Server ERRORLOG, I found the following:

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.

Reference#4: https://msdn.microsoft.com/en-us/library/ms191153(v=sql.110).aspx#Defaults

Scenario: The SPN maps to the built-in account (for example, Local System).
Default Authentication: Local (only) connections use NTLM.

Since there are no remote connections, Kerberos is not used (and probably does not matter).

 

So the non-registration of SPN is probably a non-issue in this case.

Edited by kennr
Link to comment
Share on other sites

Hello.

 

At the last moment someone told me to remove the "USE [my_db];" from the sql string. Having done that the simple test query then succeeded. I could barely believe it.

I still do not understand why rs.State=0 for the Server.Create("ADODB.Recordset") and yet the rs.State=1 for the rs.Open (sql1,conn)? Thought both the create recordset object state and rs.Open state both had to be 1? Apparently not.

I very much appreciate your efforts to help. Things are better now. Thank you.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...