Adding logins through Query Analyzer

Ok, this one is way out there so I'm not sure anyone will have witnessed this
before.
On several occasions, I have noticed that if I set up a SQL Server level
login and grant permissions to this login (either server-level or database
roles, doesn't matter which) through Query Analyzer and then don't use the
account right away (say for a couple of weeks or more), if I then try to use
this login it fails due to authentication failure. If I reset the password,
it works fine. If I use the account to connect right after I set it up, it
works fine.
Has ANYONE ever witnessed this? It's the weirdest thing.
Thanks.
Also, if I go into Query Analyzer and run sp_password to change the password
again, it will only accept the old password as being NULL.
|||Passwords usally dont change or expire on their own (In SQL Server they
will apply to domain policies, but not in 2000) ?
If you are logged in as a sa, you can reset the password without knowing the
old one by passing NULL to the SP.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Ana" <Ana@.discussions.microsoft.com> schrieb im Newsbeitrag
news:AF003637-D917-4F54-800E-A36EEE7B8FF0@.microsoft.com...
> Also, if I go into Query Analyzer and run sp_password to change the
> password
> again, it will only accept the old password as being NULL.
|||Yes, I know that being logged in with sa will allow me to change the password
without knowing the old one...I am having to do this now. My point was that
this is the only way it will allow me to change the password. I was just
verifying that SQL Server seriously doesn't know what the original password
is. I can't login with it, and I can't change the password with the original
password.
|||> SQL Server seriously doesn't know what the original password
> is. I can't login with it,
Well, if it has changed, this makes sense. If it has not, then surely you
are spelling it wrong, or transcribing from the wrong yellow sticky on your
monitor.
In summary, one of the following is true:
(a) the password has changed
(b) you are not using the correct password
SQL Server doesn't just forget what the password is.

> I can't change the password with the original password.
Why do you need the original password to change it? Log in as a local
administrator and issue:
EXEC sp_password NULL, 'oldPassword', 'sa'
Now try logging in as sa again, with the old password that SQL Server
"doesn't know"... a penny says it works.
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
|||Ok, I guess I need to spell this out...
First of all, I said this has happened on several occasions; it's not just a
one-time slip up of either the fingers or the "yellow sticky".
Second, we are a large company with several SQL Servers and from time to
time, we have the need to set up a uniform service account on all of our
servers that one application can use. To do this, we use sp_addlogin and
sp_addsrvrolemember or sp_adduser/sp_addrolemember statements and run the
exact same set of statements on each server. Oftentimes, due to project
timelines and delays on someone else's end, we set up these service accounts
and they don't get used for weeks. This is the ONLY time we see the issue of
not being able to login with the documented information (when the account has
sat for an extended period of time without ever being used).
Third, when we set something like a service account up on multiple servers,
the first server is always tested to make sure that the documented login
information works to authenticate to the server. Then the remainder of the
accounts are set up, using the same statements that were used to create the
account on the first server.
Fourth, we are a Sarbanes-Oxley affected operation. What this means is
there are only two people in our company (myself and the other SQL Server
DBA) that have sysadmin accounts to all the servers and no one besides us
knows the passwords to any of dbo service accounts (of which there are few).
What this means is, only we would be able to change the passwords for the
service accounts and we have far more important things to do than to sabotage
ourselves.
I hope this offers a more complete explanation of what we're seeing.
|||> we have far more important things to do than to sabotage ourselves.
I wasn't suggesting this. But your explanation of this just doesn't add up.
Your server suddenly doesn't know its own SA password?

> I hope this offers a more complete explanation of what we're seeing.
This doesn't explain to me how SQL Server suddenly "forgets" the SA
password. As I said before, Sarbanes-Oxley, perfect people, what have you,
one or both of the following is true:
(a) the password has changed
(b) you are not using the correct password
|||Where did I ever say that it was forgetting the SA password?
"On several occasions, I have noticed that if I set up a SQL Server level
login and grant permissions to this login (either server-level or database
roles, doesn't matter which) through Query Analyzer and then don't use the
account right away (say for a couple of weeks or more), if I then try to use
this login it fails due to authentication failure."
|||I'm guessing if you'd read the original posting and then followed the chain
through, maybe you wouldn't have been so confused. I didn't mention any
account names because this has happened several times to more than one
account.
In any case, my question to your comment "SQL Server doesn't just lose
passwords, something else is causing this" is: Have you ever tried doing
what I've described as our process (create a SQL Server level login and grant
it permissions to whatever through Query Analyzer, DON'T USE THAT ACCOUNT for
at least a few weeks, then try to use it to connect to SQL Server after
several weeks have passed) and can verify that this definitely would never
happen?
Also, I'm telling you quite explicitly, that we have not changed the
password and we are using the password that was set.
Yes, I know this sounds extrordinary, but that's why I've put a posting out
about it.
|||"Maybe if you had mentioned the actual login name you were using"
Sheesh! It's always entertaining to watch people respond to posts without
reading the OP's message. Lest I'm on drugs I couldn't see anywhere where Ana
specified the SA account, indeed the phrase used was "set up a SQL Server
level login". If you want to assume the OP meant SA then that's your problem
laddy'o... And you know what they say about assumption: makes an XXX out of u
and me