July 22, 2015 at 11:42 pm
Hello,
I have a procedure where Login and Password of the database are to be used and right now i have given a valid hardcoded value for them.
Can you please suggest how can i get these values from the actual login ( i mean sql server authentication value or windows authentication).
Thanks in advance
July 22, 2015 at 11:50 pm
Like I have got the user name from SYSTEM_USER. How do i get the password?
July 23, 2015 at 4:06 am
Please if anyone can suggest something...
July 23, 2015 at 4:10 am
You don't.
For SQL authentication, SQL stores the password hashed, the plain text is not available anywhere. If it's windows authentication, then the domain controllers have the hashed password and all SQL sees is a kerberos ticket
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 23, 2015 at 4:18 am
yes. This is what i am stuck with.
I have to use bcp ulpoads in a proc.
like -
SET@JOBSTR = 'Exec master..xp_cmdshell' + ' ' + '''bcp' + ' ' + @DB_NAME + '..table1 IN' + ' ' + @File_Path + ' ' + '-S' + @SERVER_NAME + '' + ' ' + '-c' + ' ' + '-t;' + ' ' + '-U' + @LoginID + ' ' + '-P' + @LoginPassword + ''',no_output'
execute(@JOBSTR)
So there is no way no remove the hardcoded passwords to be set?
July 23, 2015 at 4:29 am
You don't want hardcoded passwords unless you want a security nightmare. It's horrible security practice.
How about using trusted connection and ensuring that the SQL Server service account has login rights to the other SQL server? Or set up a SQL Agent proxy account, run the job under that account and ensure that the proxy account has access to the remote SQL Server.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 23, 2015 at 4:31 am
will be making sure that there are only trusted connections.
Just wanted to check if i am missing something if i write the password.
thanks a lot. 🙂
July 23, 2015 at 4:34 am
Naina_11 (7/23/2015)
will be making sure that there are only trusted connections.
??? The script you posted uses SQL authentication, not trusted connections. Trusted connections don't send username and password, they use the -E parameter to denote that windows authentication is in use.
Just wanted to check if i am missing something if i write the password.
Yes, good security practices.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 24, 2015 at 12:34 pm
Is your SQL code part of a SP?
Who calls that SP, is it interactive or not?
Depending on your situation, you might have some of the following options:
a) Call your SQL code from an application written in a compiled language (e.g. C++) or, at least, C#,
hard-code the password there and pass it to your SQL code.
b) Write an encrypted SQLCLR UDF that will just return your hard-coded value.
If you are in SQL Azure, the V12 version has support for SQLCLR:
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply