 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / General / Security / May 2008Msg 15404: Could not obtain information about user
Hello, We have two build servers running Windows Server 2003 R2 SP2 and default instances of SQL Server 2005 SP2, I'll refer to them as ServerA and ServerB. Both servers are members of our only domain (MyDomain) and each SQL service runs as a domain account (ServerASqlUser and ServerBSqlUser) which is a member of the default group Domain Users only. Until recently a service on both servers, running as a Domain Admin user BuildAdmin, would regularly execute database create and load scripts. BuildAdmin is also a member of the sysadmin role on both servers. This works fine. This week the daemon running as BuildAdmin was changed to run as a new user, BuildUser, which is a member of Domain Users only. BuildUser was also added to the sysadmin role on both SQL servers. However this fails now on both servers. During the load script, a trigger created 'WITH EXECUTE AS OWNER', is failing with the following message: Msg 15404, Level 16, State 19, Procedure MyTable_MyUpdateTrigger, Line 0 Could not obtain information about Windows NT group/user 'MYDOMAIN\BuildUser', error code 0x5. This is odd because the daemon is connected to SQL and running the create and load script as MYDOMAIN\BuildUser and I have verified that MYDOMAIN\BuildUser is the db_owner of the database. The servers have been restarted. The two domain controllers have been restarted. I have added BuildUser to each build servers' local Administrators group. Nothing helps. Can you please advise what may be causing this or what else I can do to further track and debug this issue to find a solution? Regards, -- Jason Hi Jason, I understand that when you changed your DAEMON startup account from MYDOMAIN\BuildAdmin to MYDOMAIN\BuildUser, you encountered the error when loading the script in which a trigger created "WITH EXECUTE AS OWNER". If I have misunderstood, please let me know. To track the cause of this issue, I need to collect more information from you: 1. Is MYDOMAIN\BuildUser a singleton user account or a user group? OWNER must map to a singleton account and cannot be a role or group. 2. What is the type of the trigger MyTable_MyUpdate, a DML trigger or DDL trigger? and What does it do? OWNER cannot be specified for DDL triggers. 3. The SQL error logs which are located in the folder %ProgramFiles%\Microsoft SQL Server\MSSQL(.x)\MSSQL\LOG by default. You may mail me (changliw_at_microsoft_dot_com) the logs for further research. From the error code, it indicated an "Access denied" error. I also recommend that you run Process Monitor to monitor your daemon and SQL Server process to see if there are any clues regarding file access. Of course, you can also mail the trace logs to me. You can download Process Monitor at this link: Process Monitor v1.33 http://technet.microsoft.com/en-us/sysinternals/bb896645.aspx?PHPSESSID=d926 bdd849b5aab10f7263dd7f5904f2 Look forward to your response. If you have any other questions or concerns, please feel free to let me know. Best regards, Charles Wang Microsoft Online Community Support =========================================================== Delighting our customers is our #1 priority. We welcome your comments and suggestions about how we can improve the support we provide to you. Please feel free to let my manager know what you think of the level of service provided. You can send feedback directly to my manager at: msdnmg@microsoft.com. =========================================================== Get notification to my posts through email? Please refer to http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif ications. Note: The MSDN Managed Newsgroup support offering is for non-urgent issues where an initial response from the community or a Microsoft Support Engineer within 1 business day is acceptable. Please note that each follow up response may take approximately 2 business days as the support professional working with you may need further investigation to reach the most efficient resolution. The offering is not appropriate for situations that require urgent, real-time or phone-based interactions or complex project analysis and dump analysis issues. Issues of this nature are best handled working with a dedicated Microsoft Support Engineer by contacting Microsoft Customer Support Services (CSS) at http://msdn.microsoft.com/subscriptions/support/default.aspx. ============================================================  SignatureThis posting is provided "AS IS" with no warranties, and confers no rights. ========================================================= Hi Charles, To answer your questions: 1. BuildUser is a "singleton" user account just as the previously working BuildAdmin user is. 2. The trigger is a DML on-update auditing trigger to put the current time into a column on the row being updated. 3. I have emailed the errorlog. Also note, that I have been able to replicate the behaviour outside of the daemon process. By RDPing to the servers as BuildUser, opening SSMS and executing the same .sql file the daemon uses. Also, in SSMS as BuildUser, I can replicate the same error message simply with this one line: EXECUTE AS LOGIN = 'MYDOMAIN\BuildUser' Thanks for your help, -- Jason Hi Jason, Thank you for mailing me the SQL error logs. From the error logs, I found the following error message: "2008-05-19 09:13:34.29 Server The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x2098, state: 15. 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." This indicates that the Kerberos authentication was not established for your SQL Server instance. In this case, any domain user account will not be accepted as a valid login account. I recommend that you try manually registering SPN according to this article: How to make sure that you are using Kerberos authentication when you create a remote connection to an instance of SQL Server 2005 http://support.microsoft.com/kb/909801/en-us Hope this helps. If you have any other questions or concerns, please feel free to let me know. Have a nice day! Best regards, Charles Wang Microsoft Online Community Support ========================================================= Delighting our customers is our #1 priority. We welcome your comments and suggestions about how we can improve the support we provide to you. Please feel free to let my manager know what you think of the level of service provided. You can send feedback directly to my manager at: msdnmg@microsoft.com. ========================================================= SignatureThis posting is provided "AS IS" with no warranties, and confers no rights. ========================================================= Hi Charles, I noticed that same entry in the error log when I emailed to you, did some searching and found the same KB article and implemented the SPN manually. However, even after rebooting the server and forcing a connect via TCP both locally running SSMS on the server and also remotely from a workstation, I cannot establish a Kerberos connection, only NTLM. I have added the ServerASqlUser account to Domain Admins and rebooted ServerA. This resolves the error in the sql script but it is still connected as NTLM and I'm sure you can appreciate I don't want the SQL service account to be a domain administrator. Removing the sql service user from Domain Admins and rebooting again and the original error returns. Running SetSPN -L ServerA is still showing the presence of the SPN entry. Perhaps there is some minimum privilege I can grant the sql user to fix this issue? Regards, -- Jason Hi Jason, I reviewed our posts here and I noticed that you said the following in your initial post: "Both servers are members of our only domain (MyDomain) and each SQL service runs as a domain account (ServerASqlUser and ServerBSqlUser) which is a member of the default group Domain Users only.". I am not sure that I totally understand your meaning of "the default group Domain Users", however I would like to check with you now whether you explicitly specify domain name before ServerASqlUser and ServerBSqlUser. If not, I recommend that you explicitly specify "MYDOMAIN\ServerASqlUser" and "MYDOMAIN\ServerBSqlUser" as your SQL Server startup account. You can open Services from Control Panel, double click your SQL Server service for your SQL Server instance, switch to the Log On tab and then you can verify the settings. Regarding your question, "Perhaps there is some minimum privilege I can grant the sql user to fix this issue?" I am not sure of your trigger contents, so it is hard to make a decision for defining the minimum privilege here, however I do think that you could work around this issue by executing your trigger as a user which maps to a SQL login. For example, if you have a SQL login named "sql_builduser" with the corresponding user named "sql_builduser" on your database, and assign it with the db_owner role on your database, and then you can specify "WITH EXECUTE AS 'sql_builduser'" to your trigger definition. Please try it and see if it helps. If you have any other questions or concerns, please feel free to let me know. It is my pleasure to be of assistance. Best regards, Charles Wang Microsoft Online Community Support ========================================================= Delighting our customers is our #1 priority. We welcome your comments and suggestions about how we can improve the support we provide to you. Please feel free to let my manager know what you think of the level of service provided. You can send feedback directly to my manager at: msdnmg@microsoft.com. ========================================================= SignatureThis posting is provided "AS IS" with no warranties, and confers no rights. ========================================================= Hi Charles, The SQL services are definitely configured to run as MYDOMAIN\ServerASqlUser and MYDOMAIN\ServerBSqlUser. There are definitely no local users of the same name on the servers. These two users are members of MYDOMAIN\Domain Users, a security group that seems to automatically exist on all new domains and all new users are normally automatically a member of. I have changed the trigger causing the error to be defined as: CREATE TRIGGER [dbo].[MyTable_MyUpdateTrigger] ON [dbo].[MyTable] WITH EXECUTE AS OWNER AFTER UPDATE AS SELECT 1 And the error still ocurs. I have compiled the minimum script necessary to reproduce the error on my servers and emailed it to you. Thanks for your persistence. Regards, -- Jason Hi Jason, Thank you for your email response. I got the script. Now I am performing research and may need more time to get back to you. I appreciate your patience. If you have any other questions or concerns, please feel free to let me know. Best regards, Charles Wang Microsoft Online Community Support ========================================================= Delighting our customers is our #1 priority. We welcome your comments and suggestions about how we can improve the support we provide to you. Please feel free to let my manager know what you think of the level of service provided. You can send feedback directly to my manager at: msdnmg@microsoft.com. ========================================================= SignatureThis posting is provided "AS IS" with no warranties, and confers no rights. ========================================================= Hi Jason, Thank you for your detail response. I think that there are two questions left in front of us: 1. Why did Kerberos authentication have not been established in your SQL Server instance? 2. Can this issue be worked around by using a SQL user account if Kerberos authentication could not be setup? Regarding the 1st question, I looked through our posts again and I am sorry that I omitted some points of your description. I noticed that you could work around this issue by adding your SQL Server service account to domain administrators and registering the SPN with the domain user account. This is expected since only a domain administrator account or the local system account has the required permissions to register an SPN. By default, SQL Server will try to register SPN for itself with its service account at startup, therefore, if the SQL Server service is started under a non-administrator account, SQL Server cannot register the SPN for the instance. Your concern was that you did not want to have your SQL Server service run under a domain admin account. This can be guaranteed. Actually you can register SPN for your SQL Server instance with a different domain user account that is a domain admin, but leave your SQL Server service running under a normal domain user account. Please try this again. Regarding the 2nd question, I think that you can try either of the following two methods to see if they help: 1. If your database owner is a domain user account, run "sp_changedbowner 'sa'" to change your database owner to the SQL login account. You can check what your database owner is by right clicking your database in SSMS and click Properties, and then you can find Owner field under General tab; 2. Create a SQL login account [sqltest] and assign the fixed database role db_owner to it on your database, and then run "CREATE TRIGGER [dbo].[MyTable_MyUpdateTrigger] ON [dbo].[MyTable] WITH EXECUTE AS 'sqltest'" Hope this helps. If you have any other questions or concerns, please feel free to let me know. It is my pleasure to be of assistance. Best regards, Charles Wang Microsoft Online Community Support ========================================================= Delighting our customers is our #1 priority. We welcome your comments and suggestions about how we can improve the support we provide to you. Please feel free to let my manager know what you think of the level of service provided. You can send feedback directly to my manager at: msdnmg@microsoft.com. ========================================================= SignatureThis posting is provided "AS IS" with no warranties, and confers no rights. ========================================================= |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.