SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


System dsn connection to SQL server 2008


System dsn connection to SQL server 2008

Author
Message
SQLdba-617800
SQLdba-617800
SSC-Addicted
SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)

Group: General Forum Members
Points: 434 Visits: 165
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..
K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (23K reputation)

Group: Moderators
Points: 23570 Visits: 1917
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
SQLdba-617800
SQLdba-617800
SSC-Addicted
SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)

Group: General Forum Members
Points: 434 Visits: 165
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.
K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (23K reputation)

Group: Moderators
Points: 23570 Visits: 1917
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
SQLdba-617800
SQLdba-617800
SSC-Addicted
SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)

Group: General Forum Members
Points: 434 Visits: 165
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.
K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (23K reputation)

Group: Moderators
Points: 23570 Visits: 1917
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
SQLdba-617800
SQLdba-617800
SSC-Addicted
SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)

Group: General Forum Members
Points: 434 Visits: 165
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.
K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (23K reputation)

Group: Moderators
Points: 23570 Visits: 1917
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
K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (23K reputation)

Group: Moderators
Points: 23570 Visits: 1917
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
SQLdba-617800
SQLdba-617800
SSC-Addicted
SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)

Group: General Forum Members
Points: 434 Visits: 165
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search