Security question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Security question

Author

Topic

chris_cs
Posting Yak Master

223 Posts

Posted - 2011-08-10 : 07:30:37

Hi,

I just wondered how people tackle users who use tools such as Access and Excel to access databases?

We have some applications which connect to the production servers using Windows Authenticaion, but we're struggling to 'lock down' other users such as developers who say they need access. They are no longer going to be given SSMS but they can still use other means of querying the data via Access etc..

Junior DBA learning the ropes

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-08-10 : 07:58:06

Create a developer/reporting database. Just take a backup of the production database, restore it on a regular basis to some stinky server you have laying around and let them play with that instead. And possibly revoke their permissions on the prod database...

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/

chris_cs
Posting Yak Master

223 Posts

Posted - 2011-08-10 : 08:18:04

I wish it was as simple as that. They need to have access to the Production server but we're unable to limit them to say using a specific application.

I guess my question is are there any ways you can prevent someone from using an application such as Excel and Access?

My initial thoughts were no, so the only other thought I had was maybe using end points for the production apps and only allowing connections over those.

Does that sound feasible?

Junior DBA learning the ropes

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-08-10 : 08:41:34

Hm, then maybe a logon-trigger (http://msdn.microsoft.com/en-us/library/bb326598.aspx) will do the trick? Something like this perhaps? ->

CREATE TRIGGER drop_excel_connections
ON ALL SERVER 
FOR LOGON
AS
BEGIN
IF APP_NAME() LIKE '%Excel%'
    ROLLBACK;
END

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/

chris_cs
Posting Yak Master

223 Posts

Posted - 2011-08-10 : 08:42:40

Ah, that's interesting. I might have a look at giving that a go.

Thanks for the suggestion!

Junior DBA learning the ropes

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-08-10 : 08:48:40

Instead of a ROLLBACK it would probably be better with a RAISERROR and a proper error message saying that connecting through excel/etc has been actively refused. Just dropping the connection might leave people working on trying to connect for days.

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/

chris_cs
Posting Yak Master

223 Posts

Posted - 2011-08-10 : 08:59:59

Good point. I'll work that into any trigger I test.

Thanks

Junior DBA learning the ropes

chris_cs
Posting Yak Master

223 Posts

Posted - 2011-08-10 : 09:05:15

I just had a thought. You can include the application name in a connection string so it could be spoofed.

This type of trigger would still help in limiting connections though, as the servers aren't external facing.

I just know the sneaky devs would think of this!

Junior DBA learning the ropes

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-08-10 : 12:38:09

Just curious, why do you care which application is being used? If a User has rights to read a table, for instance, why does it matter how they connect?

=======================================
I have never met a man so ignorant that I couldn't learn something from him. -Galileo Galilei, physicist and astronomer (1564-1642)

chris_cs
Posting Yak Master

223 Posts

Posted - 2011-08-11 : 05:40:02

Because of the way the application was setup the users have dbo access so they can create objects etc. As I mentioned, we are taking away SSMS but they can still connect using office products.

There is even one guy who caused havoc by creating a large amount of tables in temp db.

In my opinion this shoudn't be allowed to happen and they should get a ticking off but there is a bit of a politics issue.

We're just looking for a clever way of restricting the access.

Junior DBA learning the ropes

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-08-11 : 07:58:05

If they need access, then why are you taking away SSMS?

Removing their access makes more sense than taking away their tools.

Or take away dbo rights. Grant only the permissions required and you're problem is solved.

All anyone needs to write to your database is a command prompt and a connection.

Figure out who needs what, create Active Directory groups, add users, create login(s) for the group(s) and explicitly set permissions.

chris_cs
Posting Yak Master

223 Posts

Posted - 2011-08-11 : 09:01:19

I agree with everything you've said but we can't remove DB owner rights due to the way the application works.

When we eventually have the capacity we'll provide the dev guys with a read only copy of the database on a reporting server so they can query that.

I was just exploring what the other possibilities were.

Junior DBA learning the ropes

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-08-11 : 09:34:39

Can change the password and not give it to the developers. Then create a new login for them.

Store app connection strings where they can't access it (though a programmer can work around that too).

If you're going to use a logon trigger, better to check for hostname instead of application.

chris_cs
Posting Yak Master

223 Posts

Posted - 2011-08-11 : 10:43:56

Thanks for the suggestions.

I'll have a play around with these and see how I get on.

Junior DBA learning the ropes

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources