?
Solved

NT authentication / Active Directory

Posted on 2005-03-30
5
Medium Priority
?
635 Views
Last Modified: 2007-12-19
I wrote a VB application that uses SQL Server DTS packages and stored procedures.  The app pops up a login form and uses the UserID and Password supplied by the user to pass to SQL Server when loading DTS packages and when creating ADO connections.

Now I have been asked to eliminate the login form and instead use NT authentication or Active Directory (which the company uses) so SQL Server will recognize the user (or the group to which user belongs?) from the network login.  I've been researching this but I'm confused as to how to proceed.

So far, I have created a new user in Active Directory, and I have added the SQL Server instance to AD.

The goal is to transparently give the user access to a specific database on the server so they don't have to enter login info when they run this app, and to make security maintenance more streamlined.

Also, here are examples of the lines of VB code I think will have to change.  Can you provide guidance on this?

DTS:
            Call dtsExport.LoadFromSQLServer(strDTSServer, strUID, strPwd, _
                DTSSQLStgFlag_Default, , , , strDTSMainPackageName)
ADO:
            cn.ConnectionString = "DSN=MyDatabase;UID=" & strUID & ";PWD=" & strPwd

If SQL Server is not expecting a userID and pwd, can I just leave them out of these calls?

Thanks for your help!

0
Comment
Question by:wlevy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 13

Accepted Solution

by:
danblake earned 1000 total points
ID: 13662818
Hi wlevy,

You're nearly there..
SQL Server is not expecting a user ID / pwd combination; instead it needs to know that you wish to use Integrated Security (NT Authentication).
This is normally by adding to the cn.Connectionstring "Trusted_Connection = True" or "Integrated Security = SSPI"

If you ever forget youre connection string or want to know the difference between vendors/types try: www.connectionstrings.com

Cheers!
0
 

Author Comment

by:wlevy
ID: 13665735
Great!

My code now looks like this:

ADO
cn.ConnectionString = "DSN=MyDatabase;Trusted_Connection=True;UID=" & strUID

DTS
Call dtsExport.LoadFromSQLServer(strDTSServer, strUID, , DTSSQLStgFlag_UseTrustedConnection, , , , strDTSMainPackageName)

Works like a charm.  Thanks for your help.  I'm awarding point to you.

I am getting strUID by calling the Win32 API GetUserName function.  Is that the best way to do this?

0
 
LVL 13

Expert Comment

by:danblake
ID: 13669554
You do not need the userid usually when using a trusted connection; the connection normally takes the userID automatically from the source of the connection being used.
0
 

Author Comment

by:wlevy
ID: 13671804
Thank you, Dan, you're absolutely right.  My boss is happy!  :)

- Walter
0
 
LVL 13

Expert Comment

by:danblake
ID: 13671909
Not a problem, glad to help.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question