Solved

Windows Auth. Mode Question

Posted on 2013-12-04
2
273 Views
Last Modified: 2013-12-07
Hello friends,

I've been digging around the 'net trying to figure out the answer to this, but I'm still not 100% certain about whether I understand what I read so wanted to get some confirmation here as well as a little advice.  Thanks in advance for your patience.

I'm venturing into the world of programming with .NET (wpf app, VB) and am creating an application which needs to both read and write data to a SQL Server.  My hope was to create the code in such a way that it follows this process on opening:

Step 1: Connect to database using what I call the "Admin Connection String".  Here is what that currently looks like:

connectionString="Data Source=myServerName;Initial Catalog=myDBName;Trusted_Connection=True;User ID=domainname\adminUserName;Password=Password

Step 2: Check the server to see if a login exists for the current AD User (currently logged in user... which is a different user than I want to use for my admin connection string).  If not, create the user.

Step 3: Check the server for the appropriate database role to use the application I'm building.  If the currently logged in user doesn't have that role, grant it using sp_addrolemember.

Step 4: Close the "Admin Connection" and reopen a database connection using the user's (now appropriately created and permissioned) credentials.  The app then does other stuff which isn't relevant to this problem.

So, I think it's obvious that the problem I want to solve is to have my program grant permissions and create the login for the user if it doesn't currently exist.  To do that, I need to open the connection to the SQL Server with Admin level credentials.  

At a basic level, all the steps above I currently have working, the problem I'm having is that the program always wants to login with the currently logged in (to the computer) AD User, rather than using the "Admin User" in the above connection string.  I think this is because a SQL Server set to use Windows Authentication can't even utlize the "UID" and "PWD" portions of the connection string... am I correct in that?

If I am correct, is there any better way to approach what I'm trying to do that I'm missing?  Or should I switch the server to Mixed Auth., which I think would fix this problem?

Thanks so much!!!
0
Comment
Question by:AccessGuy1763
2 Comments
 
LVL 15

Accepted Solution

by:
jorge_toriz earned 500 total points
ID: 39697571
Well, I will write this message according your list:

1. If you specify Trusted_Connection=true in your connection strings, this means that you want to use Windows Authentication.  The keywords User Id and Password are used to authenticate with a SQL Server Login.    You must use Windows Authentication xor SQL Server Authentication, I wrote xor because the election is mutually exclusive.

If you want to use specific Windows credentials you must impersonate your program so it will pass this credentials to SQL Server.

2, 3, & 4. If the connection raise an error then you could use a default login and then create the required login, map de user and use the sp_addrolemember stored procedure to make it db_datareader.
0
 
LVL 4

Author Comment

by:AccessGuy1763
ID: 39703513
Thanks for the comment, sorry it took so long but I've been busy diving into the deep end of  WPF and MVVM and let's just say that I have MUCH to learn :).

I ended up switching the server to Mixed Mode and this allows my application to connect to the SQL Server as an Admin user on application launch.  It checks for a login for the user, a database user, and the proper role (I made one) for the application database.  If any of those things are missing, it creates/grants them and, in the end, drops the Admin connection and uses the user's own/new credentials moving forward.

Best of all, since I'm learning the MVVM method, I have made a CurrentUser class which inherits my CheckGrantDBPermissions class so everything will be very reusable moving forward! :)

Thanks again for the nudge in the right direction!!
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

932 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now