kennr Posted January 4, 2017 Share Posted January 4, 2017 (edited) 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 aninformational 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 startupaccount is believed to have enough permissions to register the SPN, but that may be for an sql expert to decide. The SQL Server isstarted 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 makecomplete 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).aspxWhere 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 January 4, 2017 by kennr Link to comment Share on other sites More sharing options...
justsomeguy Posted January 4, 2017 Share Posted January 4, 2017 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 More sharing options...
kennr Posted January 9, 2017 Author Share Posted January 9, 2017 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 More sharing options...
justsomeguy Posted January 9, 2017 Share Posted January 9, 2017 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 More sharing options...
kennr Posted January 12, 2017 Author Share Posted January 12, 2017 (edited) 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/295017Reference#2: http://geekswithblogs.net/technetbytes/archive/2011/10/09/147233.aspxReference#3: http://serverfault.com/questions/76715/windows-active-directory-naming-best-practices Edited January 12, 2017 by kennr Link to comment Share on other sites More sharing options...
justsomeguy Posted January 12, 2017 Share Posted January 12, 2017 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 More sharing options...
kennr Posted January 13, 2017 Author Share Posted January 13, 2017 (edited) 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 localhost10.99.0.1 DC1.samdom.example.com DC1---------------------------------------------------------------------------------------------------------------------------------------------------------------------Reference#1: https://msdn.microsoft.com/en-us/library/ms942145(v=cs.70).aspxReference#2: https://msdn.microsoft.com/en-us/library/ms943015(v=cs.70).aspxReference#3: https://docs.oracle.com/cd/E19575-01/820-3746/gisdn/index.htmlReference#4: https://wiki.samba.org/index.php/Setting_up_Samba_as_an_Active_Directory_Domain_Controller#Preparing_the_Installation--------------------------------------------------------------------------------------------------------------------------------------------------------------------- Edited January 13, 2017 by kennr Link to comment Share on other sites More sharing options...
justsomeguy Posted January 13, 2017 Share Posted January 13, 2017 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 More sharing options...
kennr Posted January 14, 2017 Author Share Posted January 14, 2017 (edited) 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-closedReference#2: https://msdn.microsoft.com/en-us/library/ms130978.aspxReference#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#DefaultsScenario: 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 January 14, 2017 by kennr Link to comment Share on other sites More sharing options...
kennr Posted January 16, 2017 Author Share Posted January 16, 2017 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 More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now