NT authenticated db owner
-
Hi everyone
I've recently created an NT login to SQL Server and made this login a db owner. The idea was to have this login alias to dbo. The user could not gain access to SQL Server, the error message stated that he didn't have access rights. However, as administrator, when I tried to grant him access rights to the database, an error message popped up saying that he already had those access rights as db owner. When I change the login to SQL authentication, this all works fine.
Could someone please explain the reasons for this?
Many thanks
-
No matter what authentication the SQL Server is set for (Windows Only or Mixed Authentication), Windows Authentication is always allowed.
The user's login must be specifically set to Windows Authentication.
You seem to have done all that. Now, is the user's computer part of the same domain as the SQL Server? Windows Authentication requires a "trusted connection". That means the domain controller(s) used by the two systems must be able to authenticate each other.
It sounds like you may not have a trusted connection between the two systems.
-SQLBill
-
Hi,
I agree with SQL Bill. Could you also post error messages here?
I have a comment: Why would anyone want to make a Windos login a dbo? I have a checklist of what and when does not work because of that. Here are some samples based on the most common usage cases:
1.Domain changes due to mergers or just Infrastructure changes. Then jobs would not run, including backup jobs if Windows login is an owner of a job. Database owner is unknown and dbo is not mapped to anyone (no dbo user) if this login was a database owner
2. Developers get copies of the production database on their computers. Different domain, different part of the network. The implications: first, see #1, second, I have to remap SIDs Do not also forget ownership chaning
3. Windows Database Owner gets another job. His Windows login is disabled now. See #1 and #2.
Regards,Yelena Varsha
-
Thank you very much guys.
The application needs to modify database objects and therefore db owner, however we need to track more accurately who's doing what, therefore NT authentication. The whole group would know the SQL login and so we will not be able to track individuals.
I have resolved the problem since my last post. Yes it is 1 domain. The solution is to detach the database, recreate the NT login and reattach the database as that id, then the NT login becomes the db owner. The application can function properly and we can trace individuals' activities.
The points you have raised are very valid. Thank you for that.
Kind regards
Brenchia
-
Our standard is to use NT authentication everywhere we can. This is mainly because NT auth gives improved security over SQL auth.
All but 1 of our production instances only allow NT login - the other runs a few vendor applications that still require SQL authentication.
We have no problems in working this way.
There are additional things that DBAs have to learn on top of SQL authentication to get NT auth to work smoothly, but none of them are rocket science. Personally, I find it hard to understand why any site would want to compromise the security of SQL Server by allowing SQL authentication for anything.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
-
Ed,
For Windows Authentication to work across domains you need to have trusted networks. That isn't always available. My database is accessed by users in multiple domains and for other security reasons they are not trusted domains. Therefore, we use SQL Authentication.
-SQLBill
-
Stander,
If you want to map an id to dbo, execute sp_changedbowner 'domain\user'. You can also mention a sql id here instead.
Bill,
You can use the runas command with netonly. The -netonly option will take you out of the current domain and will allow you to use windows authentication to work across domains which are not trusted. Below is the syntax.
runas /netonly /user:domain\user_name "mmc /s \"C:\Program Files\Microsoft SQL Server\80\Tools\BINN\SQL Server Enterprise Manager.MSC\""
-
Sa24,
Thanks for the syntax. I really like this thing.
Ed,
I was talking not about Windows Authentication versus SQL Authentication. Windows Authentication for users is good, but explicitly about making a Windows login a DBO (Database Owner) as well as Job owner or Object Owner in general. Permanent objects like Production databases or backup jobs or Stored Procedures should not depend on disposable things like Domain Names or Windows (Domain user) SIDs.
Yelena
Regards,Yelena Varsha
-
SA24,
Good suggestion except for one thing. As I said we don't have trusted network for a reason. Specifically it's a security issue.
NETONLY doesn't exist as a command on our systems. Nor can a normal user use the RUNAS command. Our systems are VERY locked down. (Normal user can't even open the command prompt).
-SQLBill
-
Try using the following:
net use \\servername\ipc$ /user:otherdomain\otheruseraccount
Since you can't open a command prompt, try throwing it into a batch job and run from there. If the passwords between the two accounts don't match you will be prompted. This assumes you have a windows account in both domains.
Other option is to use the password option in the command and hard-code it in the job (bad idea in my opinion)
Jason
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply