November 15, 2011 at 2:23 pm
I know for a fact that my SA pw has been changed. However, our SOX IT auditors want me to send them a screenshot "proving" that the SA pw has been changed. Can someone tell me the best way to show this visually?
Thank you.
Edit: Sorry, this was supposed to be in one of the SQL Server 2008 forums. I just noticed I accidentally put it in here with 2005 stuff. Maybe the same applies.
November 15, 2011 at 2:54 pm
Try logging in with 'sa' and no password and screenshot the login dialog and the login failed error box?
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
November 15, 2011 at 5:58 pm
You need to use the PWDCOMPARE function:
Reat this article: http://msdn.microsoft.com/en-us/library/dd822792.aspx
Use it like this,
SELECT name FROM sys.sql_logins
WHERE PWDCOMPARE('', password_hash) = 1
OR PWDCOMPARE('', password_hash, 1) = 1 ;
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
November 16, 2011 at 4:10 pm
Presumably if the password in sys.syslogins is null then it hasn't been changed, and if not it has?
Also what is the password policy set to, wouldnt the corerect setting here prove it must have been changed?
Andrew
November 17, 2011 at 11:08 am
You can right click on the properties for the SA user and show a screen shot that the password is not empty, even though it does not show for security reasons, it still does show that it is not an empty string.
November 17, 2011 at 11:41 am
Leo, maybe my eyes just cannot make out the difference but where is the difference from your WHERE line and the OR line condition? Should one of the entries have a space in them?
HTH -- Mark D Powell --
November 17, 2011 at 1:03 pm
The OR line has an extra parameter. Note the extra ,1 in the brackets.
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
November 17, 2011 at 1:09 pm
Now that you have pointed it out the extra 1 is plan as daylight. Here is my version of the query you posted.
select
name
,default_database_name
,is_disabled
,modify_date
from
sys.sql_logins
where
pwdcompare('',password_hash) = 1 or
pwdcompare('',password_hash,1) = 1
order by
name
go
Thank you
-- Mark D Powell --
November 17, 2011 at 2:03 pm
Tested on 2005 and 2008 successfully. It errors on 2000 (no sys.sql_logins table after all)
-- Mark --
November 17, 2011 at 4:42 pm
SKYBVI (11/17/2011)
@leo & markThe script provided by you not working.
Can you confirm it is working on your side?
Regards,
SKYBVI
This only works on SQL 2005 up. I've tested it and I know we use it regulalry as we do SQL Server Health checks as one of our service offerings. One of the tests is for blank passwords and this is the method we use. In SQL 2000 you could check the length of the password, but from 2005 on this no longer works.
Cheers
Leo
SQL Services Ltd
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
November 18, 2011 at 6:04 am
Leo.Miller (11/15/2011)
You need to use the PWDCOMPARE function:Reat this article: http://msdn.microsoft.com/en-us/library/dd822792.aspx
Use it like this,
SELECT name FROM sys.sql_logins
WHERE PWDCOMPARE('', password_hash) = 1
OR PWDCOMPARE('', password_hash, 1) = 1 ;
Cheers
Leo
Leo....Doesn't this script tell us about the logins who have no passwords on sql 2005 /2008 ...
But the Original poster asked if hte password was changed, then he wants to know.....
Thats why, I told that the script didn't work ( As i manually changed the password of a login and then executed the script expecting it to select that login but it didn't).
Regards,
SKYBVI
Regards
Sushant Kumar
MCTS,MCP
November 18, 2011 at 6:42 am
I think the posted query answers the question that was asked which I remember as being how to prove to the auditor via a query that the sa account requires a password, that is, it has been changed from null.
IMHO -- Mark D Powell --
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply