SQL Alert: login

Showing posts with label login. Show all posts

Tuesday, March 6, 2012

Access through MS Access ADP for everyone?

Hello,
We have recently installed SQL enterprise manager. I was testing the securi
ty to the databases when I realized that if I login to XP under a standard d
omain user account, open MS Access, create an ADP project, then under connec
tions, pick the sql server, I can see and pick any of the databases and then
I can open and view any of the tables in any of the databases. How can thi
s be possible? We are using NT authentication. This user has no account in
SQL and is just a domain user.It's allowed somehow with the security you have implemented
on the server, in the databases. It's not clear what version
or edition of SQL you are running, where the SQL Server
instance is installed - on the network and you are accessing
this over a network? It's not clear what operating system
the SQL Server is running on, is the SQL Server in a domain
or is this actually a workgroup? Did you change any of the
default security settings? Who are members of Local Admins
where SQL Server is installed? What is the status of the
guest account in Windows where SQL is installed?
What databases are you actually accessing and opening
tables? Are these system databases?
-Sue
On Tue, 11 Oct 2005 21:19:32 -0400, "Jack"
<jackhnospam@.jackandjay.com> wrote:

>Hello,
>We have recently installed SQL enterprise manager. I was testing the security to t
he databases when I realized that if I login to XP under a standard domain user acco
unt, open MS Access, create an ADP project, then under connections, pick the sql ser
ver
, I can see and pick any of the databases and then I can open and view any o
f the tables in any of the databases. How can this be possible? We are usi
ng NT authentication. This user has no account in SQL and is just a domain
user.

Saturday, February 25, 2012

Access restriction

I want to access mssql2000 server only by 'sa' & database login ids. I want to restrict windows administrator account to access the database. Please help !!!
Our enviorment :- SQL2000 with SP3 on Windows2000 advanced server with latest service pack..Create new login under the Logins node in EM and add "domain Admins" group. Select "Deny Access" under the Authentication option.
You can also restrict the group to individual databases by adding it in a particular database and assinging it specific roles(deny datareader,deny datawriter) etc.|||What's the SQL Server Agent running under?

Builtin\Admin?

In that case it will have sa rights..

You need to create a new account, give it sa, change the sql server agent to run with that

And remove auth to builtin...builtin still needs to be around to start up the box...

Oh and buy the book SQL Server 2000 ADMIN 911

Talks all about it...

Friday, February 24, 2012

Access Permissions on server scoped objects for login

We are having problems with the response times from UPS WorldShip after switching from SQL Server 2000 to 2005.

I think that the problem can be fixed from the database end by setting the permissions correctly for the user/role/schema that is being used by WorldShip to connect to the server but, I'm not sure how to do it.

The Setup

Client
UPS WorldShip 8.0 running on XP Pro SP2
Connecting via Sql Native Client via SQL Server Login
Connection is over a T1 via VPN

Server -
SQL Server Standard Edition on Windows Server 2003
2x3ghz Xeon processors w/ 4gb ram

The user that is being used to connect runs under it's own schema and role and only needs access to two tables in a specific database on the server.

What UPS WorldShip seems to be doing is on a continual basis retrieving information about the layout of the database via calls such as the following

exec [sys].sp_tables NULL,NULL,NULL,N'''VIEW''',@.fUsePattern=1

exec [webservices].[sys].sp_columns_90 N'CHECK_CONSTRAINTS',N'INFORMATION_SCHEMA',N'dbase name',NULL,@.fUsePattern=1

exec [webservices].[sys].sp_columns_90 N'COLUMN_DOMAIN_USAGE',N'INFORMATION_SCHEMA',N'dbase name',NULL,@.fUsePattern=1

This seems to happen whenever WorldShip contacts the database to find out information in order to be able to create a mapping to the database as well as exporting information to it. Because of the VPN connection these calls take anywhere from 20 seconds to 3 minutes.

I am fairly confident that the problem lies with these calls to the database which I was able to capture using the SQL Server Profiler. We have experimented with the following setups.

1. Connecting to SQL 2000 over VPN with SQL Native Client - No noticeable lag
2. Connecting to SQL 2000 over VPN with SQL Server 2000 driver - No Noticable lag
3. Connecting to SQL 2005 locally with SQL Native Client - No Noticable lag
4. Connectiong to SQL 2005 over VPN with SQL Native Client - Lots of lag

Our network admin has been testing the network connections over the VPN and it is very responsive with none of the long wait times found when using UPS WorldShip.

Now for a possible solution other than getting UPS to fix their software. I think that by limiting the tables and views that the login is able to see will cut down significantly on the lag times that are being experienced. The problem is that there were 264 items that were being returned by sp_tables. I was able to cut that down to 154. I am unable to disable access to any of the rest of the items because they are server scoped.

Take for example the INFORMATION_SCHEMA.CHECK_CONSTRAINTS view. When I try to deny access to it in any way I get the following error:

Permissions on server scoped catalog views or system stored procedures or extended stored procedures can be granted only when the current database is master (Microsoft SQL Server, Error: 4629)

Am I able to deny access to these types of object and if so how? Also, what objects should be accessable such as sys.database_mirroring, sys.database_recovery_status, etc?

Yes, you should be able to deny access to server scoped objects. Create a user in master that is mapped to same login as that of your database user and deny the select permissions to that user. I hope the below example will make it more clear.

Let the database name be dbname, login used to connect to the server be lgn1 and the user corresponding to login lgn1 in dbname be usr1. If i understand your scenario correctly, lgn1 connects to the database dbname and runs the query

exec [sys].sp_tables NULL,NULL,NULL,N'''VIEW''',@.fUsePattern=1

You have denied select permissions on various views to usr1 and managed to reduce the number of objects returned to 154. Now to get it down further , try the following steps.

Connect to the master database and create a user for login lgn1.

use master

create user usr_master for login lgn1

Next, deny the select permissions on the various object to this user usr_master

DENY SELECT ON INFORMATION_SCHEMA.CHECK_CONSTRAINTS to usr_master.

Repeat for every server scoped object you want to disable access.

Let me know if this works for you

|||Weird. I thought I had tried that. Maybe I didn't link the user to the login. Anyway, it seems to have worked.

You don't by chance have any idea as to what should stay accessible to a client.
|||

Check the TABLE_OWNER column returned by sp_tables. If the TABLE_OWNER is INFORMATION_SCHEMA or SYS, then those views return metadata about the server. If your client absolutely does not require access to any metadata of the server then you can disable access to all objects whose TABLE_OWNER is INFORMATION_SCHEMA or SYS.

Access Permissions on server scoped objects for login

We are having problems with the response times from UPS WorldShip after switching from SQL Server 2000 to 2005.

The Setup

Client
UPS WorldShip 8.0 running on XP Pro SP2
Connecting via Sql Native Client via SQL Server Login
Connection is over a T1 via VPN

Server -
SQL Server Standard Edition on Windows Server 2003
2x3ghz Xeon processors w/ 4gb ram

The user that is being used to connect runs under it's own schema and role and only needs access to two tables in a specific database on the server.

What UPS WorldShip seems to be doing is on a continual basis retrieving information about the layout of the database via calls such as the following

exec [sys].sp_tables NULL,NULL,NULL,N'''VIEW''',@.fUsePattern=1

exec [webservices].[sys].sp_columns_90 N'CHECK_CONSTRAINTS',N'INFORMATION_SCHEMA',N'webservices',NULL,@.fUsePattern=1

exec [webservices].[sys].sp_columns_90 N'COLUMN_DOMAIN_USAGE',N'INFORMATION_SCHEMA',N'webservices',NULL,@.fUsePattern=1

I am fairly confident that the problem lies with these calls to the database which I was able to capture using the SQL Server Profiler. We have experimented with the following setups.

Our network admin has been testing the network connections over the VPN and it is very responsive with none of the long wait times found when using UPS WorldShip.

Take for example the INFORMATION_SCHEMA.CHECK_CONSTRAINTS view. When I try to deny access to it in any way I get the following error:

Permissions on server scoped catalog views or system stored procedures or extended stored procedures can be granted only when the current database is master (Microsoft SQL Server, Error: 4629)

Am I able to deny access to these types of object and if so how? Also, what objects should be accessable such as sys.database_mirroring, sys.database_recovery_status, etc?

Create a user for that login in the master database. Let's say that the login is 'alice', then you have to do something like this:

use master

create user alice

deny select on INFORMATION_SCHEMA.CHECK_CONSTRAINTS to alice

This needs to happen in the master database because CHECK_CONSTRAINTS is a special catalog. You can query it in other databases, but in fact it resides in the resource database and permissions on it have to be granted within the master database. You can see the permissions granted on it by querying the database_permissions catalog in the master database:

select * from sys.database_permissions where major_id = object_id('INFORMATION_SCHEMA.CHECK_CONSTRAINTS')

The deny made in master will work in any other database.

What objects should be accessible depends on what the UPS WorldShip application needs to access. You could try to determine the required subset of objects for which access should be granted to the application by trial and error.

Thanks
Laurentiu

|||Mostly correct.

you would have to add

create user alice for login alice

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=407644&SiteID=1&mode=1|||Thanks by the way.|||

You don't need the "for login alice" part in my example because the user that is created has the same name as the login, so SQL will know to map them correctly. Asvin's example uses the clause because the user he creates is named differently than the login to which it is mapped.

Thanks
Laurentiu

|||Hi guys,

I have the same problem with WorldShip 8.0 but I'm using SQL Server 2000 and it just started happening last week.

I was wondering... I thought I had blocked access to all normal tables in the DB the WorldShip is querying, but somehow it is still able to run sp_tables and sp_columns and it takes forever to do so. I am also running it over a VPN.

The "create user xyz in master" syntax doesn't seem to work in SQL Server 2000.

I have created a role for the UPS computer user (ups) and restricted it to SELECT only from the one VIEW in needs to and INSERT into only the one table it needs to however it can still run the sp_tables and sp_columns commands.

Could someone please help me block its access to these two stored procedures?

Thanks!

Robert|||

CREATE USER is new DDL added in SQL Server 2005. The SQL Server 2000 counterpart is sp_adduser.

Thanks
Laurentiu

|||Thanks for that...

I was wondering. I am trying to block access to the sp_tables and sp_columns stored procedures but it doesn't seem to let me. The "public" role has access to run these but I set the permission for the UPS user to deny but it doesn't seem to work. Is there some way for me to do this?

Thanks.|||

How did you deny the permissions? The permissions should be denied in the master database to the UPS user.

use master

DENY EXECUTE ON sp_tables to UPS

DENY EXECUTE on sp_columns to UPS

|||Thanks,

I tried that and it's still able to run them somehow. ARGH!! Oh well, back to the drawing board.|||

I assume there is a login called UPS. And this login is mapped to a user called ups in each user database. What you are trying to do is to ensure that when the login UPS connects to any database, it cannot execute the procedures sp_tables and sp_columns. I hope I understood your scenario correctly.

If the above is correct, then here's what you should do. Create a user called UPS mapped to the login UPS in the master database. You can do this by

use master

CREATE USER UPS FOR LOGIN UPS

Now deny the execute permissions on this user UPS in the master database.

use master

DENY EXECUTE ON sp_tables to UPS

DENY EXECUTE on sp_columns to UPS

It shouldn't matter what permissions are granted to public since DENY to a specific user should override the grant on the public role.

Also, can you run the below queries in the master database and let me know the results. These queries return the permissions on the procedures.

select user_name(grantee_principal_id), * from sys.database_permissions where major_id = object_id('sys.sp_tables')

select user_name(grantee_principal_id), * from sys.database_permissions where major_id = object_id('sys.sp_columns')

|||Asvin,

Thanks a lot for your reply!

I've done exactly what you say. I've verified that the user has EXECUTE denied and it does. However it is still able to run sp_tables.

Unfortunately, since I'm running SQL Server 2000, your test queries don't work. Is there a 2000 version equivalent to the sys.database_permissions?|||

Ah! Didn't realize this was sql server 2000. The query in sql server 2000 should be

select user_name(grantee), object_name(id),* from master.dbo.syspermissions where id=object_id('dbo.sp_tables')
select user_name(grantee), object_name(id),* from master.dbo.syspermissions where id=object_id('dbo.sp_columns')

|||Thanks again Asvin!

Here are the results of those two queries. I found that the best way to show you was with a screenshot, so here is the URL:

http://rob.densi.com/sqlquery.jpg

I'm not quite sure what I'm looking at, but maybe you can tell me if things are set up properly.

Thanks!

Robert|||

You are looking at the permissions that have been granted or denied on the stored procedures sp_tables and sp_columns. To drill down further into what the permissions are can you run these queries

select object_name(id),user_name(uid),* from sys.sysprotects where id=object_id('dbo.sp_tables')

select object_name(id),user_name(uid),* from sys.sysprotects where id=object_id('dbo.sp_columns')

To interpret the results of this query use the documentation at

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sys-p_0837.asp

If you see that the protecttype column is set to 206 for the user UPS and UPS is still able to execute these procedures, some thing is really wrong. Can you also try connecting directly to the master database as UPS and run these stored procedures instead of connecting to a user defined db.