November 10, 2011 at 11:07 am
Hi All,
We use an application that uses windows authentication and the same account is also used on the SQL Server 2008 database. So user logs into the App X with their domain account and in the background App X uses that same domain account to access the SQL database.
What I want to do is to setup SQL logins for some users (who need database access from remote locations, etc) that have a Windows login and to save them the trouble of remembering yet another password I would like to somehow apply the password they use for their Windows (domain) login to their SQL login. Is this possible?
I realise that since authentication is done via an AD the password will be stored on the AD controller - can I somehow fetch that password and then apply it to the SQL login? Or is there a built-in feature in SQL Server (or AD) that natively supports this?
Thanks!
November 10, 2011 at 11:41 am
you cannot retreive the password;
but you can pass the crendentials from the domain to the SQL server(is the SQL server on the domain?)
you would get rid of your SQL logins and simply replace them with a group (or individual) Windows logins instead.
then you change your app to use a trusted connection instead of passing the SQl username and password:
Private Const SqlConnectionFormat As String = "data source={0}; _
initial catalog={1}; _
user id={2}; _
password={3}; _
Trusted_Connection=False; _
Connect Timeout=600; _
Workstation ID=GhostInTheMachine; _
Application Name=HaxxorPadPlusPlus;"
Private Const SqlTrustedConnectionFormat As String = "data source={0}; _
initial catalog={1}; _
Trusted_Connection=True; _
Connect Timeout=600; _
Workstation ID=GhostInTheMachine; _
Application Name=HaxxorPadPlusPlus;"
Lowell
November 10, 2011 at 12:01 pm
Thank you for the reply Lowell.
I think I should clarify - the SQL Logins are purely intended to allow a limited number of "privileged" users to access to database via their preferred SQL tool (most likely SSMS or Access/Excel using ODBC). And the PC's that these users are on are not on the same domain (and in fact do not even use AD for authentication).
This is the current setup:
User logs in to their PC using local account or Novell account
User launches application and uses a different username and password that is authenticated on a AD domain (that the SQL box is also on).
User launches SSMS and will enter their SQL login details to access the DB. They cannot use Windows authentication (i.e. use same account as above) because their PC is on a different domain (and in fact a different a network; these are remote users).
So if I understand correctly the solution you provided is on the application side. What I need is something that can be done on the database side, i.e. when I setup the SQL logins I want to be able to say "create user XYZ password = MYDOMAIN\XYZ's password". If that makes sense! 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply