• Status: Solved
  • Priority: High
  • Security: Public
  • Views: 59
  • Last Modified:

Connect to SQL Server without directly having credentials

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?
0
Russ Suter
Asked:
Russ Suter
  • 5
  • 3
  • 2
  • +3
1 Solution
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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/
0
 
Russ SuterAuthor 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.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> 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.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Russ SuterAuthor Commented:
Not even close. In your very first sentence you basically said exactly the opposite of what I was asking.
0
 
Adam BrownSr Solutions ArchitectCommented:
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.
0
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
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.
0
 
Russ SuterAuthor 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.
0
 
Adam BrownSr Solutions ArchitectCommented:
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.
0
 
Russ SuterAuthor 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.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> 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..
0
 
Geert GOracle dbaCommented:
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
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
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
0
 
Russ SuterAuthor Commented:
That's a start. Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

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

  • 5
  • 3
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now