May 14, 2007 at 4:29 am
I'm a bit stumped by an issue with SQL logins and passwords.
I am auditing the SQL logins on a server and have identified logins with blank (null) passwords and those where the password is the same as the user name. So I thought that those not identifed as either blank or having user name and password the same would have a valid password.
But, I have now identifed numerous SQL logins where the password is not blank, but the users can login without entering a password.
Can anyone shed some light on how this can happen?
May 14, 2007 at 7:44 am
AFAIK, this cannot happen with SQL logins. Are you sure they're not using Windows auth?
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 14, 2007 at 9:02 am
I can confrim that the logins in question are SQL logins.
As you will be aware, using EM to create a login with a blank password adds a null to the password field in syslogins, but using SP_addlogin , [Logins name], '' will create a login where the password is recorded as a zero length string. This it seems is the situation in my case.
select * from master..sysxlogins
where password is null
returns the null passwords
select * from master..sysxlogins
where PWDCOMPARE('', password, 0)=1
returns zero length passwords
May 15, 2007 at 8:45 am
are those SQL logins mapped to an server level login which uses Windows authentication? If so, you'll never need the password for the sql database level login. Correct me if I am wrong here, but if sql level logins are created without being mapped to a SQL database server level ID, then users cannot 'connect' to the database as that sql login (not considering the public role or guest accounts, but guest accounts should be turned off unless necessary to view data).
There is a stored procedure, i am thinking sp_helplogins, which should show you all server level logins and their mapping to the database sql logins. Also, have you checked permissions on the public role and guest accounts?
May 16, 2007 at 3:41 am
If the user's Windows login and a SQL Server login are identical, and the user has rights to login to SQL Server via their Windows account, then you will get this behaviour. The user may (should!) get their Windoes login rights via group membership.
If this is happening, the login is being fully authenticated, using Windows authentication. A login using Windows authentication always takes precedence ofer a SQL Server login, so in this case the SQL Server login is not actually getting used.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
May 16, 2007 at 4:02 am
I agree with the last two posts, and Steve's earlier response: I had just this situation when I converted an MS Access database to SQL2K tables linked to Access and secured (I thought!) by SQL login. I was never prompted to authenticate.... but then I was also sysadmin via my Windows login (I wasn't too baffled for too long...<duh>!). At least my users have no server permissions via the Windows domain, and get the SQL login prompt.
May 16, 2007 at 4:15 am
I would reiterate that the logins are solely SQL logins, there is no corresponding Windows login that would allow the user to access the server/database.
The situation came to light when I was looking specifically for SQL logins with blank passwords, generally these will be identified by running
select * from master..sysxlogins
where password is null
This did not identify all of the SQL logins that could access the database(s) without entering a SQL password. Having fished around a bit it is apparent that if a SQL login is created via Enterprise manager with the blank password option being checked, the entry in the syslogins table will have the value NULL in the password field. If however the login is created via Query Analyser using SP_addlogin , [Logins name], '' , then the password appears as a zero length string, so it is not identified by
select * from master..sysxlogins
where password is null
Rather they can be identified by
select * from master..sysxlogins
where PWDCOMPARE('', password, 0)=1
May 16, 2007 at 6:45 am
You don't need server permissions. If TCP/IP is enabled, SQL has listening ports (1443, i think) that you can point your ODBC driver to and use access to update data.
May 16, 2007 at 6:46 am
Basically, if the users have no server logins, they can't connect to data. You need a server level login thats linked to a sql login to connect to the database.
Have you checked the level of permissions on the public role yet?
May 16, 2007 at 8:15 am
The original poster is talking about logins. Database access is through USERS, not logins. There is a mapping, but if you call them both logins you'll get confused.
If a user has a non-blank password, then they need to enter a password. The only thing I could see here is two users having the same name for some reason (broken data integrity ?) and one has a blank password.
If you really think that login Steve, with password "StevePassword" can login with that password or a blank, then you need to call PSS and get this fixed. I can't believe it's the case, but if it is, it's a HUGE problem.
And please post a followup if you do.
Using two different queries and trying to match up data is problematic. Please check all your data from syslogins and provide a specific example of a login and what happens at login if we're missing something.
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 16, 2007 at 8:51 am
OK, firstly, it's not that big an issue for me, I just though it was interesting.
Anyhoo, to illustrate the issue let us create 3 SQL logins as follows
EXEC sp_addlogin 'xxx'
EXEC sp_addlogin 'xxxx', ''
and xxxxx created via enterprise manager selecting the option for a blank password
Then let us look at the password firl in syslogins by running the following queries
select convert(varbinary(255),password) from syslogins
where name = 'xxx'
Result = Null
select convert(varbinary(255),password) from syslogins
where name = 'xxxx'
Result = 0x0100CE5E4510A5390C9F3FEE559E40EB57D08B9B1117A9ACF20DA5390C9F3FEE559E40EB57D08B9B1117A9ACF20D
select convert(varbinary(255),password) from syslogins
where name = 'xxxxx'
Result = Null
All 3 can login without entering a password.
There are numerous example scripts floating around that can be used to audit passwords, mainly these rely on something along the lines of
SELECT
left(name, 20) AS 'Login Name' ,
'Blank password'
FROM master.dbo.syslogins
WHERE password IS NULL
AND isntname = 0
to identify the blank passwords, this will return the logins xxx and xxxxx, but not xxxx. The latter can be identified by
SELECT
left(name, 20) AS 'Login Name' ,
'Zero length string in password'
from master..syslogins
where PWDCOMPARE('', password, 0)=1
Like I say, not a big issue, just a bit curious.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply