Home / server / Questions / 179589
In Process
Ian Mackinnon
Asked: 2010-09-10 07:23:19 +0800 CST

How to configure Windows user accounts for ODBC network with NT authentication?

  • 772

I'm trying to create a connection to an SQL Server database from the ODBC Data Source Administrator using "Windows NT authentication using the network login ID". Both server and client are running Windows XP.

It appears that any account with administrator privileges can add the data source on the server*, though connection attempts from the client result in error messages that suggest it is trying to authenticate using a guest account.

I found a Microsoft support page that says:

For SQL Server...: connect using the impersonated user account.

But it doesn't offer advice about how to do that.

How do I impersonate a user account on the server?

or (since it sounds like that would lead to an unfortuante squashing of privileges and loss of accountability):

How do I give an account on the client privileges on the server database and then ensure the client attempts authentication with the privileged account and not with a guest account?

I'm aware that I'm providing rather sparse information. This is because I'm in unfamiliar territory and don't know what's pertinent. I'll attempt to add any requested information as quickly as possible.

*I'm planning on tightening privileges straight after I get it working as it stands.

sql-server windows-xp authentication odbc dsn

2 Answers

  1. It sounds like you'd get some benefit from documentation describe the "basics" behind the security system in Microsoft SQL Server.

    I'd have a look at these docs relating to principals, permissions, and securables to get a feel for how you can apply permission for users/groups to access objects in a granular fashion in SQL Server.

    Those docs are a bit abstract, but they're the nitty-gritty details.

    Getting away from Microsoft, there's a really nice "crib sheet" that Robyn Page wrote that gives good background on the security model.

    For a 10,000 foot view, what you're looking to do is create Active Directory groups (which you'll make users members of) to which you'll grant various permissions on resources ("securables") hosted by the SQL Server computer. What specific permissions and securables you'll be dealing with depends on your specific application. If certain users need UPDATE access to certain tables, or the ability to execute certain stored procedures, you'll use SQL Management Studio (or, ugh, Enterprise Manager, if you're on SQL Server 2000 or older) to grant the desired permissions.

    • 2

  2. Are the SQL server and the Xp workstation in the same domain? if this is a direct xp workstation to SQL it should be using the credentials specified (from a differing domain) not guest. The article you point to is talking about reporting services- that is a whole different beast. The 2 important factors there are:

    1. Ensuring that the users accounts have access to the proper database
    2. Ensuring that the report server computer account is trusted for delegation.

    I suspect that if this is a reporting server issue question that #2 is your problem. In order for a server to use credentials from another server it needs to delegate authentication. The steps required are listed here. Once trusted a server can then impersonate a user account.

    • 0