May 26, 2011 at 5:22 am
Tom.Thomson (5/26/2011)
nigel. (5/26/2011)
Steve,Thanks for that question, I'd was not aware of 'loginproperty' until now.
One question though. I tried to recreate your scenario but always get '1900-01-01 01:00:00.000' as the result. Anyone have any ideas why?
Maybe you are not running on Windows Server 2003 (or 2008?), or don't have both CHECK_POLICY and CHECK_EXPIRATION enabled?
Thanks Tom,
That'll be it. I'm not running on Windows Server.
May 26, 2011 at 5:44 am
Typos aside, it was a good question. If you read the BOL entry, you couldn't just read the answer, you had to think about what you were reading and deduce the answer. Hope we get more questions like this one. Thanks!
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn
May 26, 2011 at 5:47 am
I also try the command before (from the time I got SQLserver, I certainly logged once with a bad password!) and after doing a new login with a bad password.
I got "NULL"...
I tried an other 'property_name' : SELECT LOGINPROPERTY('sa', 'DefaultLanguage');
I got the right answer...
It is like I never got my login password wrong, wich is impossible
Why do I get NULL for the 'property_name' BadPasswordTime with my SQLserver2008?
I checked CHECK_POLICY and CHECK_EXPIRATION enabled, wich was not checked. How long do I need to wait or do I need to restart SQLserver to work?
May 26, 2011 at 6:04 am
Carlo Romagnano (5/26/2011)
In sql2005, I tried with other logins than sa and I get always the following:1900-01-01 02:00:00.000
Step 1: bad password attempt
Step 2: correct login
I run the query: SELECT loginproperty('carlo', 'badpasswordtime')
The result: 1900-01-01 02:00:00.000
Note the hour 2
?????
Got the same with SQL 2008 R2 running on Windows 2008 R2 Server.
I'm assuming you're located in Italy, so you're on Central European Summer Time (CEST) = UTC / GMT + 2 hrs.
Thanks to Tom Thomson's explanation about setting CHECK_POLICY = ON (this should set CHECK_EXPIRATION = ON automatically), I was able to see the date and time of the bad login attempt.
Thanks,
Michael
May 26, 2011 at 6:24 am
michael.kaufmann (5/26/2011)
Carlo Romagnano (5/26/2011)
In sql2005, I tried with other logins than sa and I get always the following:1900-01-01 02:00:00.000
Step 1: bad password attempt
Step 2: correct login
I run the query: SELECT loginproperty('carlo', 'badpasswordtime')
The result: 1900-01-01 02:00:00.000
Note the hour 2
?????
Got the same with SQL 2008 R2 running on Windows 2008 R2 Server.
I'm assuming you're located in Italy, so you're on Central European Summer Time (CEST) = UTC / GMT + 2 hrs.
Thanks to Tom Thomson's explanation about setting CHECK_POLICY = ON (this should set CHECK_EXPIRATION = ON automatically), I was able to see the date and time of the bad login attempt.
Thanks,
Michael
Thank you, that's true.
But the strange thing is that for sa the query returns effectively the right date and time, for the user Carlo, despite the settings, no. :blink:
May 26, 2011 at 6:28 am
Nice easy question, from experience.
May 26, 2011 at 6:44 am
Steve,
I didn't know you had a SQL Server time machine? 2011, then 2010? Will you please use it to see if the Mayans are right for the end of the world next year? 😛
Fix minutes is how long it takes someone to fix something. Sometimes I'm asked as to how long it will take to fix something (on the very rare occasion where something needs fixing) and I respond "fix minutes". If it takes 2 hours, I report it as 120 fix minutes. So, I knew exactly what Steve was talking about. :hehe:
May 26, 2011 at 6:51 am
Didn't know about this property, but the answer made sense. Not an easy question, but a logical one.
More like that please 🙂
Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems
May 26, 2011 at 6:53 am
Carlo Romagnano (5/26/2011)
Thank you, that's true.But the strange thing is that for sa the query returns effectively the right date and time, for the user Carlo, despite the settings, no. :blink:
Do you mean that for SA you get e.g. 2011-05-26 14:15:37 and for CARLO you get 1900-01-01 02:00:00?
The CHECK_POLICY setting has to be enabled for each individual login.
Regards,
Michael
May 26, 2011 at 8:44 am
Apologies on the year. I've corrected that.
I should have noted this is OS dependent. My apologies for that. From the explanation URL: NULL if the CHECK_POLICY or CHECK_EXPIRATION is OFF for a login, or if the operating system does not support the password policy.
My suspicion is that NULL is not returned, but the base SQL Server date of 1900-1-1
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
May 26, 2011 at 9:48 am
Nice question. I think the answer is logical.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 26, 2011 at 10:06 am
Do you mean that for SA you get e.g. 2011-05-26 14:15:37 and for CARLO you get 1900-01-01 02:00:00?
Yes, exactly!
May 26, 2011 at 10:07 am
Steve Jones - SSC Editor (5/26/2011)
Apologies on the year. I've corrected that.I should have noted this is OS dependent. My apologies for that. From the explanation URL: NULL if the CHECK_POLICY or CHECK_EXPIRATION is OFF for a login, or if the operating system does not support the password policy.
My suspicion is that NULL is not returned, but the base SQL Server date of 1900-1-1
Now you see how long this question's been in the queue! :w00t::hehe::laugh:
May 27, 2011 at 3:05 am
Carlo Romagnano (5/26/2011)
Do you mean that for SA you get e.g. 2011-05-26 14:15:37 and for CARLO you get 1900-01-01 02:00:00?
Yes, exactly!
Then I'd assume that CHECK_POLICY has not been enabled for this account. Did you try adjusting the setting for Carlo?
Regards,
Michael
May 27, 2011 at 11:35 am
While, I knew what it was suppose to do, I tested this out on my own box and somehow it wasn't recording the datetime of the bad password attempt??? Not sure what's going on there.
Anyone else see this?
Dennis Parks
MCSE, MCDBA, MCSD, MCAD, MCTS
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply