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!!!