Block remote login

How can I block users from using their SQL accounts to access SQL 2000 server
remotely thru enterprise manager or sql query analyzer? Appreciate any help.
Thanks.
Hi,
There is no methodology in SQL Server 2000 to restrict the remote access; if
the users have access to network. The only way to restrict
is by setting complex password to the SQL Account.
Thanks
Hari
SQL Server Mvp
"Benny" <Benny@.discussions.microsoft.com> wrote in message
news:0EE6279A-1977-4B3E-A4AB-EC1719C2B447@.microsoft.com...
> How can I block users from using their SQL accounts to access SQL 2000
> server
> remotely thru enterprise manager or sql query analyzer? Appreciate any
> help.
> Thanks.

Block remote login

How can I block users from using their SQL accounts to access SQL 2000 server
remotely thru enterprise manager or sql query analyzer? Appreciate any help.
Thanks.Hi,
There is no methodology in SQL Server 2000 to restrict the remote access; if
the users have access to network. The only way to restrict
is by setting complex password to the SQL Account.
Thanks
Hari
SQL Server Mvp
"Benny" <Benny@.discussions.microsoft.com> wrote in message
news:0EE6279A-1977-4B3E-A4AB-EC1719C2B447@.microsoft.com...
> How can I block users from using their SQL accounts to access SQL 2000
> server
> remotely thru enterprise manager or sql query analyzer? Appreciate any
> help.
> Thanks.

Block remote login

How can I block users from using their SQL accounts to access SQL 2000 serve
r
remotely thru enterprise manager or sql query analyzer? Appreciate any help.
Thanks.Hi,
There is no methodology in SQL Server 2000 to restrict the remote access; if
the users have access to network. The only way to restrict
is by setting complex password to the SQL Account.
Thanks
Hari
SQL Server Mvp
"Benny" <Benny@.discussions.microsoft.com> wrote in message
news:0EE6279A-1977-4B3E-A4AB-EC1719C2B447@.microsoft.com...
> How can I block users from using their SQL accounts to access SQL 2000
> server
> remotely thru enterprise manager or sql query analyzer? Appreciate any
> help.
> Thanks.

Blank password check

Is there any way to check whether the password for existing sql authentication login is blank? (except trying to connect)

Where can I find information about how the passwords are stored internally in SQL Server?

which version r u talking about. if its 2005 you can do that i believe.

credentials are stored in sysxlogins system table

Madhu

|||Only hashes are stored, you won′t find them in clear text. For SQL Server 2005 use password policies to ensure a certain password complexity.

Jens K. Suessmeyer:

http://www.sqlserver2005.de

|||Yes I understand it, I just wonder how some tools discover logins with blank passwords (http://www.ngssoftware.com/products/database-security/ngs-squirrel-sql.php)|||

Oh, actually it seems to be fairly simple,

I just call ALTER LOGIN myLogin WITH PASSWORD = N'' OLD_PASSWORD = N'' and if it succeed -- login myLogin has a blank password. Or am I missing something?

|||

sir..... you are resetting the password to null. it will change the password, it will not tell u whether its blank or not. But it will make the password blank evenif its not blank. Not atall the correct method

Madhu

|||No, trying to change the password seems to be a bit offensive :-)

I wrote this short code snippet for you, tell me if it works for you, it scans all reachable /discoverable servers and test all SQL Server logins:

Code Snippet

foreach (DataRow dr in SmoApplication.EnumAvailableSqlServers().Rows)

{

Server srv = new Server(dr["Name"].ToString());

foreach (Login l in srv.Logins)

{

if (l.LoginType == LoginType.SqlLogin)

{

SqlConnection conn = new SqlConnection(string.Format("Data Source={0};Initial Catalog=master;User Id={1},Password={2}",

srv.InstanceName,

l.Name,

string.Empty));

try

{

conn.Open();

Console.WriteLine(string.Format("The userid {0} has an empty password.", l.Name));

}

catch (SqlException ex)

{ }

finally

{

if (conn.State == ConnectionState.Open)

conn.Close();

}

}

}

}

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Thanks Jens,

the way you suggest works fine I think.

|||

Madhu,

I cannot agree with you.

See the following script:

Code Snippet

CREATE LOGIN myLogin WITH PASSWORD = N'IT IS NOT A BLANK PASSWORD!', CHECK_POLICY = OFF

BEGIN TRY

-- this statement will FAIL because we specified invalid old (current) password

ALTER LOGIN myLogin WITH PASSWORD = N'' OLD_PASSWORD = N''

END TRY

BEGIN CATCH

PRINT 'Password is NOT blank!'

END CATCH

-- this statement will actually change the password

ALTER LOGIN myLogin WITH PASSWORD = N''

PRINT '//Password was changed to blank//'

-- so now password is blank

BEGIN TRY

-- this statement will SUCCEED because we specified right old (current) password

ALTER LOGIN myLogin WITH PASSWORD = N'' OLD_PASSWORD = N''

-- but actually nothing will happen because new password is the same as old one.

PRINT 'Password is BLANK!'

END TRY

BEGIN CATCH

END CATCH

DROP LOGIN myLogin

The idea is that ALTER LOGIN fails when called with OLD_PASSWORD option and invalid old (current) password given.

I think that it is even more effective way to check than trying to logon on server with <loginName, blankPassword> credentials.

Blank password check

Is there any way to check whether the password for existing sql authentication login is blank? (except trying to connect)

Where can I find information about how the passwords are stored internally in SQL Server?

which version r u talking about. if its 2005 you can do that i believe.

credentials are stored in sysxlogins system table

Madhu

|||Only hashes are stored, you won′t find them in clear text. For SQL Server 2005 use password policies to ensure a certain password complexity.

Jens K. Suessmeyer:

http://www.sqlserver2005.de

|||Yes I understand it, I just wonder how some tools discover logins with blank passwords (http://www.ngssoftware.com/products/database-security/ngs-squirrel-sql.php)|||

Oh, actually it seems to be fairly simple,

I just call ALTER LOGIN myLogin WITH PASSWORD = N'' OLD_PASSWORD = N'' and if it succeed -- login myLogin has a blank password. Or am I missing something?

|||

sir..... you are resetting the password to null. it will change the password, it will not tell u whether its blank or not. But it will make the password blank evenif its not blank. Not atall the correct method

Madhu

|||No, trying to change the password seems to be a bit offensive :-)

I wrote this short code snippet for you, tell me if it works for you, it scans all reachable /discoverable servers and test all SQL Server logins:

Code Snippet

foreach (DataRow dr in SmoApplication.EnumAvailableSqlServers().Rows)

{

Server srv = new Server(dr["Name"].ToString());

foreach (Login l in srv.Logins)

{

if (l.LoginType == LoginType.SqlLogin)

{

SqlConnection conn = new SqlConnection(string.Format("Data Source={0};Initial Catalog=master;User Id={1},Password={2}",

srv.InstanceName,

l.Name,

string.Empty));

try

{

conn.Open();

Console.WriteLine(string.Format("The userid {0} has an empty password.", l.Name));

}

catch (SqlException ex)

{ }

finally

{

if (conn.State == ConnectionState.Open)

conn.Close();

}

}

}

}

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Thanks Jens,

the way you suggest works fine I think.

|||

Madhu,

I cannot agree with you.

See the following script:

Code Snippet

CREATE LOGIN myLogin WITH PASSWORD = N'IT IS NOT A BLANK PASSWORD!', CHECK_POLICY = OFF

BEGIN TRY

-- this statement will FAIL because we specified invalid old (current) password

ALTER LOGIN myLogin WITH PASSWORD = N'' OLD_PASSWORD = N''

END TRY

BEGIN CATCH

PRINT 'Password is NOT blank!'

END CATCH

-- this statement will actually change the password

ALTER LOGIN myLogin WITH PASSWORD = N''

PRINT '//Password was changed to blank//'

-- so now password is blank

BEGIN TRY

-- this statement will SUCCEED because we specified right old (current) password

ALTER LOGIN myLogin WITH PASSWORD = N'' OLD_PASSWORD = N''

-- but actually nothing will happen because new password is the same as old one.

PRINT 'Password is BLANK!'

END TRY

BEGIN CATCH

END CATCH

DROP LOGIN myLogin

The idea is that ALTER LOGIN fails when called with OLD_PASSWORD option and invalid old (current) password given.

I think that it is even more effective way to check than trying to logon on server with <loginName, blankPassword> credentials.

Bizarre login behavior.

sql2k
sp3
I have two db's, each with a WINNT login named DBATest. DBATest is a member
of Security Admins as well as db_accessadmin and db_securityadmin in both
DB's. However, in one DB he can see the Users and Roles, and in the other he
cant. How can it be?
TIA, ChrisRIt turned out to be that Public was granted Select on all the System tables
in one DB but not the other. It would seem that if a user was added to
db_accessadmin and db_securityadmin, something like a trigger would make sure
the correct systems tables had Select, but oh well.
"ChrisR" wrote:
> sql2k
> sp3
> I have two db's, each with a WINNT login named DBATest. DBATest is a member
> of Security Admins as well as db_accessadmin and db_securityadmin in both
> DB's. However, in one DB he can see the Users and Roles, and in the other he
> cant. How can it be?
> TIA, ChrisR

Bizarre login behavior.

sql2k
sp3
I have two db's, each with a WINNT login named DBATest. DBATest is a member
of Security Admins as well as db_accessadmin and db_securityadmin in both
DB's. However, in one DB he can see the Users and Roles, and in the other he
cant. How can it be?
TIA, ChrisRIt turned out to be that Public was granted Select on all the System tables
in one DB but not the other. It would seem that if a user was added to
db_accessadmin and db_securityadmin, something like a trigger would make sur
e
the correct systems tables had Select, but oh well.
"ChrisR" wrote:

> sql2k
> sp3
> I have two db's, each with a WINNT login named DBATest. DBATest is a membe
r
> of Security Admins as well as db_accessadmin and db_securityadmin in both
> DB's. However, in one DB he can see the Users and Roles, and in the other
he
> cant. How can it be?
> TIA, ChrisR