Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

System dsn connection to SQL server 2008 Expand / Collapse
Author
Message
Posted Thursday, August 20, 2009 9:18 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 5, 2012 7:30 AM
Points: 42, 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..
Post #774312
Posted Friday, August 21, 2009 12:56 PM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Friday, September 26, 2014 7:52 AM
Points: 6,624, Visits: 1,873
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, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #775350
Posted Saturday, August 22, 2009 7:25 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 5, 2012 7:30 AM
Points: 42, 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.


Post #775614
Posted Saturday, August 22, 2009 7:45 PM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Friday, September 26, 2014 7:52 AM
Points: 6,624, Visits: 1,873
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, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #775615
Posted Tuesday, August 25, 2009 6:48 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 5, 2012 7:30 AM
Points: 42, 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.


Post #776614
Posted Tuesday, August 25, 2009 7:48 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Friday, September 26, 2014 7:52 AM
Points: 6,624, Visits: 1,873
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, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #776690
Posted Tuesday, August 25, 2009 12:03 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 5, 2012 7:30 AM
Points: 42, 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.


Post #776940
Posted Tuesday, August 25, 2009 12:35 PM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Friday, September 26, 2014 7:52 AM
Points: 6,624, Visits: 1,873
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, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #776979
Posted Tuesday, August 25, 2009 12:37 PM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Friday, September 26, 2014 7:52 AM
Points: 6,624, Visits: 1,873
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, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #776983
Posted Tuesday, August 25, 2009 2:00 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 5, 2012 7:30 AM
Points: 42, 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.

Post #777053
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse