DSN settings for ODBC connection to SQL 7 Server
-
I am new to this forum and I have a question regarding ODBC settings.
I am trying to set a System DSN connecting to a MSSQL 7 server using local sql server accounts, but I am trying to authenticate using a Domain account that is in active directory. I tried using the format: domain\UID but I am not able to authenticate. The reason I am trying to authenticate this way is because I have a web server that is using active directory for authentication through the web. Can anybody help?
-
Your post doesn't make much sense.
Your system DSN specifies the type of authentication (NT Auth or SQL Auth). If you are using NT Auth, then the authentication will actually occur when the connection is made.
In SQL 2000, there is an additional service that is used to integrate SQL Server with Active Directory.
I have not used it (no AD here yet), but you probably need to configure this service.
Steve Jones
-
The situation is a complicated but I will try to explain it: Our current environment is Novell with pockets of Windows NT 4 and even less Windows 2000 with AD. We are hosting a web applciation that we are trying to use AD to authenticte users. One of the apps is VB and it will use a DSN to connect to a SQL database. The client workstation is not part of the AD, therefore the client cannot use windows authentication on DSN and must use sql authentication. Is there a way to force Domain authetication from the local sql authentication prompt? "domain\uid"
-
Here's a question: will each user have their own login or are you planning on a single login to access the SQL Server from the workstation? If it's a single login, is there any reason a standard SQL login can't be used?
Part of what's confusing to me is that you say you have a web app, then you say you have a VB app running on the workstation. You do have a web app with COM/COM+ components or do you have both a web app and a VB (fat client) app? Is there a middleware component where DCOM is possible?
K. Brian Kelley
K. Brian Kelley
@kbriankelley -
There is both a web interface and a vb app. We are trying to integrated a legacy app with a web interface tool. Users are not part of the AD domain and need authentication to the AD Domain via the DSN. We are trying to authenticate using a DSN with local sql accounts but then forcing a Domain account authentication. The Web app and the VB app use COM.
-
A local sql account just will not work. Your options are either a domain account from the same domain housing your SQL box, or a SQL login from that same machine. Most web apps (and com components) use a sql login, but its not a requirement.
The key is to build your DSN and use the 'Test Connection' button at the end. If you cant get a connection there, keep experimenting until you do. No point in even trying any other code if you cant validate the DSN. I've got several articles posted on here about DSN's that you may find useful.
You can make any app prompt you for the login and/or password if you need to.
Why would you use AD to authenticate if you dont have everyone/everything in it? Can you provide some detail about why you chose/are trying to use this technique? We have AD running where I work, but for authentication nothing more than the NT login is needed, SQL resolves group memberships automatically.
Andy
-
What we are trying to do is integrate our user directories. Currently, we have three SQL Servers with different databases. The databases were put on seperate Servers because of performance considerations and security requirements. Each of these SQL Servers have there own sql local accounts for users. The web application also needs a directory of users and will pull information from the various databases for the users. The VB application also pulls information from these databases by using a System DSN. What I need to find out is there a way to authenticate users to active directory using DSN without the users' workstation belonging to AD.
-
Having the users workstation in AD would only be necessary if you are going to manage that computer with Active Directory so, no the user workstation would not have to be a part of AD.
Not trying to go backwards here but, are you possibly confusing the functionality of Active Directory with what is really NT authentication?
You can apply inherent properties to an NT account in Active Directory which will allow certain permissions to that NT account (file share permissions etc.) however, it is still using NT authentication when connecting to any server including SQL Server. If that is the case and we are crossing terminology, then yes, you can authenticate with NT autentication from a non AD managed workstation using a DSN.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
-
When I stated that the workstation was not in AD, I should of stated that it was not authenticating to a NT Domain at all it is part of a workgroup. The actual Domain that will authenticate users is in our AD. So when you try to sent up a DSN for SQL it asks you which type of authentication you are going to use: Windows NT authentication or SQL Server Authentication. If you chose NT Authentication it makes the assumption that the account you logged into the workstation with is the account to use for NT authentication. This choice would not work since the user/workstation is not part of a domain. I would like to change the settings in the DSN so that I could tell it the domain and user account but that option is not avaible. If I choose sql server authentication I can not point to a domain. Does anybody know of a work around?
-
With a sql login you can connect without a problem. For instance, to upload articles to this site I run an executable on my workstation at home that connects using a SQL login and an IP address to the server.
Im still not sure why you need to authenticate in the domain if your users arent domain members.
NT authentication is nice, but it has its drawbacks. One being that the user has the same rights whether connected via your app or if they are using MS Query. If you're trying to adjust rights based on who the user is, it's pretty straightforward to implement your own system within your app. I work on a Win2K LAN, even there it's often more effective to use a SQL login.
One other point to remember is that connection pooling only works if your connect string is the same - wont happen with NT authentication.
Andy
-
What Andy posted is what I was thinking about when I first asked the question about standard SQL logins.
Is there some limitation as to why you can't use a standard SQL login through DSN? I know most of the Microsoft literature says "use NT authentication" but there are quite a few SQL Servers out there running in mixed mode.
K. Brian Kelley
K. Brian Kelley
@kbriankelley -
I tend to agree and with Andy and Brian, but I am confused. It's been awhile since I worked with DSNs (too much of a admin pain), but as I understand it:
The DSN merely points the user in the right direction. There are no credentials stored in the DSN. If you choose NT Auth, then at connection time, the current credentials of the user are passed to the server. If they are not valid, then I assume you get a login invalid (or perhaps a dialog).
If you choose SQL auth, then the U/P must be sent at login time or a dialog is presented. Either way, for the web app, you must use SQL auth if you are going to allow users to enter U/Pwd and maintain their same permissions. If you use NT Auth, the app will always connect using the credentials of the IIS service.
Now, for AD, I am not sure what updates have been made to DSNs to handle this. My guess is they haven't done much, or they assume that the workstation must be part of the domain.
Not sure what to tell you, but my recommendation is to go with SQL Auth if you are integrating two apps.
Good luck and we'd appreciate hearing back from you if you learn something different.
Steve Jones
-
If you're using a file DSN, you can add the following lines to have it connect automatically using a SQL Server login and avoid the prompt:
UID=<username>
PWD=<password>
If you're using a User or System DSN, the prompt will occur.
K. Brian Kelley
Edited by - bkelley on 07/26/2001 1:27:55 PM
K. Brian Kelley
@kbriankelley
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply