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 don´t 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."|||Sorry, I was having trouble deciphering this:
"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."
Maybe if you had mentioned the actual login name you were using, there would
have been less confusion over which login was actually affected.
In any case, change my previous post from "the SA" to "any", and my point
remains the same. SQL Server doesn't just lose passwords, something else is
causing this.|||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 ass out of u
and me|||Hey laddy'o, why don't you look at the thread and see where I came in.
If you have some useful advice for the OP, or even a useful contribution to
the thread, why don't you submit it, instead of calling people names.
Sheesh is right.
--
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.
"Scrat" <Scrat@.discussions.microsoft.com> wrote in message
news:E8C30EA4-E3C9-4595-B1FD-8C7C66D277BE@.microsoft.com...
> "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 ass out
> of u
> and me|||Okay, good luck finding the ghost in the machine.
I'll be looking forward to seeing the actual solution. (Never mind your
admission that, yes, somebody DID change the password or re-create the login
after all.)
Heard of profiler? Might want to have it scan for events like sp_addlogin,
sp_droplogin, and sp_password.
A
"Ana" <Ana@.discussions.microsoft.com> wrote in message
news:3F2CDB76-49F8-4BE9-996A-4FEF93B0C49D@.microsoft.com...
> 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.|||Hey, you wanna jump in a thread mid point and start making assumptions then
go right ahead. Just makes you look kinda foolish is all...
Given how short the thread is it behooves one to read the initial post - and
thus save a lot of to-ing and fro-ing with the OP.
Normally I'd have said nowt but your arrogance annoyed me somewhat. A simple
apology to the OP would have helped 100%.
So, to add to the thread, I think what Ana's getting at is that this *is*
weird behavior & she mentioned that it was a couple of weeks later before the
problem transpired. You meant to leave Profiler running 24x7 to find it? Even
the blackbox prolly don't help since it'll rollover every 10MB.
Rather than picking at the OP, trying reading and helping. Or does being an
MVP put you above us mere-mortals?|||"AB - MVP" wrote:
> Okay, good luck finding the ghost in the machine.
> I'll be looking forward to seeing the actual solution. (Never mind your
> admission that, yes, somebody DID change the password or re-create the login
> after all.)
> Heard of profiler? Might want to have it scan for events like sp_addlogin,
> sp_droplogin, and sp_password.
> A
I give up. This is precisely why I never post anything; no one wants to
actually help the person having issues, but they all want to try to look
smarter than everyone else.
AB, you seem to have a fairly closed-minded approach to helping those with
sincere questions. This is clearly demonstrated by the fact that you
couldn't even answer my last question and the fact that you thought it was ok
for you to waste my time by not reading the entire thread before you started
pointing fingers. You are arrogant and rude and how you ever EARNED a title
such as MVP is beyond me.|||> Rather than picking at the OP, trying reading and helping. Or does being
> an
> MVP put you above us mere-mortals?
Of course, no matter how hard I try to help, there's some ignorant a-hole
for whom it's not good enough.
Ciao.|||Ana,
I did some googling for anything close to your problem. Dunno if this helps,
but...:
http://groups-beta.google.com/group/comp.databases.ms-sqlserver/browse_frm/thread/4ebde7376a0d78a5/8e40673af9b17e87?q=sp_addlogin+query+analyzer&rnum=3&hl=en#8e40673af9b17e87|||Hi Ana,
I have read your post.
Two quick suggestions (2 is the only one I expect to be relevant)
1) I have seen sometimes when a backup is restored that the users can get
'orphaned'. They appear in both the master and the 'restored' db but the
login does not work. Slim chance that this is the case but who knows?
2) The other thing I thought could be helpful is to place a trigger on the
password columns to log to a new table if any changes do actually occur
during the two week schedule and who did it or if somehow there is some bug
in SQL (not that they ever happen of course).
Good luck I wish it was a better suggestion but maybe it will help.
Mike LVP ;-)|||Mike,
Thanks for your response. The first suggestion doesn't really apply here
because we're not restoring users from another database, we're simply
creating new users. The second option might work. I'll discuss this with my
fellow DBA and see what he thinks.
Luckily, we don't do these "mass single user creations" all the time.
Thanks again!
Ana