Connect to SQL Server without directly having credentials

Russ Suter
Russ Suter used Ask the Experts™
on
I'm trying to figure out a way to connect to a Microsoft SQL Server using Windows Authentication without needing to log in using that account. Basically, the SQL Server should have a trusted domain account that my application can use regardless of who is actually logged in to the system. Is this possible? Is there some kind of token exchange option that might make this work?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Yes, if you want to use a Trusted connection using Windows Authentication, then this might be the connection string required..

Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;

For more other connection string options..
https://www.connectionstrings.com/sql-server/
Russ SuterSenior Software Developer

Author

Commented:
I know that. It doesn't answer my question. I'm trying to figure out how to obtain a secure credential that is maintained by Active Directory but isn't the current user.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> I'm trying to figure out how to obtain a secure credential that is maintained by Active Directory but isn't the current user.

Hope you would be having an Active Directory and if so, then whoever can access this windows or web application can login using their Windows credentials. If you want to add one more credential for functioning of the Windows or web application, then you might need to use a hardcoded windows login without Password expiry policy.
Kindly let me know whether this answers your question.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Russ SuterSenior Software Developer

Author

Commented:
Not even close. In your very first sentence you basically said exactly the opposite of what I was asking.
Adam BrownSenior Systems Admin
Top Expert 2010

Commented:
You can assign any permissions you want or need on the database, as long as you have administrative access to the database itself. You should then be able to run queries and connections using a specific user account that has been granted the necessary permission on the database. https://www.mssqltips.com/sqlservertip/2038/understanding-how-a-user-gets-database-access-in-sql-server/ should give you some guidance on how to proceed.
Nakul VachhrajaniTechnical Architect, Capgemini India

Commented:
I don't think it would be possible to use a user context without the user logging in.

What happens when we use Windows Authentication is that the user is authenticated with the AD based on the context and this is then trusted by the SQL Server.

So, you would need to login in order to setup a context to authenticate with the AD and the SQL Server. I am open to suggestions from other experts.
Russ SuterSenior Software Developer

Author

Commented:
OK, maybe I'm asking this question the wrong way. I know all about SQL Server permissions. I know all about connection strings. I know all about Windows Authentication and Active Directory. Here's the scenario I'm trying to work out.

Users Fred and Ethel both want to use a desktop, not a web, application that connects to a SQL Server database. Both users have Active Directory accounts and they are both logged in to valid workstations. The SQL Server is not set up to use SQL Server authentication. It uses Windows Authentication only. Is there a way, even possibly using a 3rd party solution, that will allow my desktop application to obtain a valid credential that I can then use in my connection string. All subsequent SQL queries will be run using the EXECUTE AS directive to differentiate database users but the actual database connection is made using a secure credential.
Adam BrownSenior Systems Admin
Top Expert 2010

Commented:
Obtain programatically? Not likely. You might be able to define a valid credential, but it's really really difficult (if at all possible) to automatically discover a valid SQL account to use without already having full administrative access to the DB. And if there's an application that can do it, it's probably more of a hacking tool than a legitimate utility.
Russ SuterSenior Software Developer

Author

Commented:
I was thinking more of a 3rd party authentication server that can provide a valid credential upon request. Kind of a key exchange server or a token server or something like that.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> I was thinking more of a 3rd party authentication server that can provide a valid credential upon request. Kind of a key exchange server or a token server or something like that

May I know more details on this specific requirement to suggest better and suggested few other things without this information earlier..
Instead of getting a valid credential upon request by 3rd party or any other mechanism, its simple and more easier to use a single defined credential right..
Oracle dba
Top Expert 2009
Commented:
something like this :  
Inside my app, i logon with a specific active directory user inside a thread
https://msdn.microsoft.com/en-us/library/windows/desktop/aa378184(v=vs.85).aspx

and then Impersonate:
https://msdn.microsoft.com/en-us/library/windows/desktop/aa378612(v=vs.85).aspx

And then logon to the database inside that thread
All actions toward the database, and servers, are done with that specific user

Doesn't matter who the frontend user is, it's always the same backend user

On a side note, I do control access to the app, by checking a table in the database
Kyle AbrahamsSenior .Net Developer

Commented:
Just a note that you can use a psexec to execute it under another user's context.
EG:
psexec \\computername -u domain\user -p password "C:\Directory\myprog.exe" 

Open in new window


Beyond that you would need to impersonate which would be handled by the program itself:
https://docs.microsoft.com/en-us/sql/relational-databases/clr-integration/data-access/impersonation-and-credentials-for-connections?view=sql-server-2017
Russ SuterSenior Software Developer

Author

Commented:
That's a start. Thanks.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial