Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

NT authentication / Active Directory

Posted on 2005-03-30
5
Medium Priority
?
637 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
  • 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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

580 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