August 20, 2009 at 9:18 am
I am trying to create a system dsn from a non domain system using a domain account.
We have a new rule in our company to connect to sql server 2008 using domain accounts. But most of our database users have non domain pcs. They are able to make the system dsn/ File dsn connection using sql server username and password. But when I try to give domain account name (domainname\username) and password its not working. I have added the domain account name in sql server and gave all the previlages.
Problem: From a non domain systems I cannot use domain account. I tried to edit the dsn file in notepad and gave the user name as domainname\username, but no luck
Please help me out..
August 21, 2009 at 12:56 pm
You can't. Using a domain account in this manner means that you are on a trusted system, so far as the domain is concerned. You must use SQL Server based-logins if your systems are not joined to the domain.
There is a workaround, but it's not pretty. What it means is that the account users are using locally must be created locally on the server where SQL Server is running and the passwords must be kept in synch. This sort of "pass-through" authentication works, but it's a bear to maintain. So your best option is to either get those systems on the same domain or to live with the use of SQL Server based logins.
K. Brian Kelley
@kbriankelley
August 22, 2009 at 7:25 pm
Both the systems cannot be in the same domain. One is production network.
Our system admin is so adament about this windows authentication and according to him there should be a way to do it.
Actually I can open management studio or odbcad32.exe from command prompt (Run as)using other windows domain account and then if I connect to sql server with windows authentication, it works. But the problem is, if we are using statistical tools like jumb then we cannot give the dsn name in that program. In that case connection will fail.
August 22, 2009 at 7:45 pm
You can make a connection from the system in question using Windows authentication? Or are you connecting from your workstation/laptop and not the server in question?
If the computer attempting the connection is not part of the same domain as the SQL Server, or part of a domain that has a trust relationship, then it is not possible except with the workaround I gave.
K. Brian Kelley
@kbriankelley
August 25, 2009 at 6:49 am
I can make a windows authentication connection from a workstation in a different domain to sql server in different domain using the following command.
RunAs /user: sqldomain\user /netonly "%systemRoot% system32/odbccad32.exe"
When I run this command in cmd prompt it will ask for the password and open the odbc connection wizard. Then I can use windows authentication in the connection method. It works. But I cannot use this method in my scenario.
August 25, 2009 at 7:48 am
Then that means there is a trust relationship, which is why your system administrator is insisting on Windows authentication. However, when you configure a System DSN, it's going to use the credentials of the user that's logged on. You can't specify different Windows credentials via the DSN.
Does your application use a service account or does it run under the context of the logged on user?
K. Brian Kelley
@kbriankelley
August 25, 2009 at 12:03 pm
Our sys admin created user service accounts for each user.
So you mean I should be able to use File dsn instead of system dsn? I think File dsn is fine too. But only demand is we should use otherdomain\serviceaccount to connect to sql server.
August 25, 2009 at 12:35 pm
No, any DSN will have this issue. Are the user accounts in a trusted domain compared to the server where SQL Server is installed? You'll need to verify this with your system administrator (or better yet, point him/her to this thread). If they are, then you'll need to grant access to the user accounts being used to log in. You can't specify Windows credentials in the DSN. It will use the logged on user. It doesn't matter if it's a user, system, or file DSN. It's all about current user context.
K. Brian Kelley
@kbriankelley
August 25, 2009 at 12:37 pm
SQLdba (8/25/2009)
But only demand is we should use otherdomain\serviceaccount to connect to sql server.
What type of app is it? If it's a web application, say ASP.NET-based, you can specify a service account. If it's a fat client a user kicks off inidividually, unless the app is written to know how to use a different set of credentials, this isn't doable (unless you want every user to have that service account). Can you talk a bit more about the application?
K. Brian Kelley
@kbriankelley
August 25, 2009 at 2:00 pm
Its actually a data analysis software called jump. They embed the sql query in the program and it uses dsn to make the connection to sql server. If I open the compiled version of the file, I can see the dsn name embedded in the code behind it.
I have added the user service account group in sql server and gave the read permission.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply