sql authentication - 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 2005 Forums

SQL Server Administration (2005)

sql authentication

Author

Topic

anas.zeta
Starting Member

6 Posts

Posted - 2012-08-12 : 05:44:17

i wanna create and set a user login that have exclusively access to specific db via "enterprise manager" and "my application" and prohibited from any windows and sql admins of sql servers accessing it from "enterprise manager" or any other method like backup files.

anas zeta

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-08-12 : 13:03:44

You can't lock out the admin.

Too old to Rock'n'Roll too young to die.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-08-13 : 01:47:59

It is possible to block based on IP. Are you able to map the potential users to certain ip ?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

komkrit
Yak Posting Veteran

60 Posts

Posted - 2012-08-14 : 06:29:37

You can achieve that goal by "LOGON TRIGGER" for SQL SERVER 2005 onward.

For example, here is logon trigger that denied accessing from SSMS(even though that login is sa)
You may modified a little bit trigger to match your requirements.

---------------
CREATE TRIGGER my_trigger
ON ALL SERVER WITH EXECUTE AS 'your_login_name'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'your_login_name'
AND (SELECT program_name FROM sys.sysprocesses WHERE SPID = @@SPID) LIKE 'Microsoft SQL Server Management Studio%'
ROLLBACK;
END;
---------------

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Komkrit Yensirikul
Currently be a DBA in worldwide hotel reservation. Experienced in top leading banking environment.

komkrit
Yak Posting Veteran

60 Posts

Posted - 2012-08-14 : 06:33:29

Another thing to concern,
SQL Server 2005 SP2 or later version can only use LOGON TRIGGER.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Komkrit Yensirikul
Currently be a DBA in worldwide hotel reservation. Experienced in top leading banking environment.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-08-14 : 07:12:21

quote:
Originally posted by komkrit

You can achieve that goal by "LOGON TRIGGER" for SQL SERVER 2005 onward.

For example, here is logon trigger that denied accessing from SSMS(even though that login is sa)
You may modified a little bit trigger to match your requirements.

---------------
CREATE TRIGGER my_trigger
ON ALL SERVER WITH EXECUTE AS 'your_login_name'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'your_login_name'
AND (SELECT program_name FROM sys.sysprocesses WHERE SPID = @@SPID) LIKE 'Microsoft SQL Server Management Studio%'
ROLLBACK;
END;
---------------

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Komkrit Yensirikul
Currently be a DBA in worldwide hotel reservation. Experienced in top leading banking environment.

That's pretty damn paranoid! -- cool though!

Doesn't stop you from accessing the db in other ways - download toad or use sqlcmd..

The best thing to do would be to change the sa password and then put the credentials in a fire proof safe.

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

anas.zeta
Starting Member

6 Posts

Posted - 2012-08-15 : 06:19:23

quote:
Originally posted by webfred

You can't lock out the admin.

Too old to Rock'n'Roll too young to die.

i need to lock out the admin, just for my db...

anas zeta

anas.zeta
Starting Member

6 Posts

Posted - 2012-08-15 : 06:20:24

quote:
Originally posted by jackv

It is possible to block based on IP. Are you able to map the potential users to certain ip ?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

no jack, i cant...

anas zeta

anas.zeta
Starting Member

6 Posts

Posted - 2012-08-15 : 06:25:07

quote:
Originally posted by komkrit

You can achieve that goal by "LOGON TRIGGER" for SQL SERVER 2005 onward.

For example, here is logon trigger that denied accessing from SSMS(even though that login is sa)
You may modified a little bit trigger to match your requirements.

---------------
CREATE TRIGGER my_trigger
ON ALL SERVER WITH EXECUTE AS 'your_login_name'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'your_login_name'
AND (SELECT program_name FROM sys.sysprocesses WHERE SPID = @@SPID) LIKE 'Microsoft SQL Server Management Studio%'
ROLLBACK;
END;
---------------

komkrit its nice way, but i need something more common for any new admin users that i didnt defined...
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Komkrit Yensirikul
Currently be a DBA in worldwide hotel reservation. Experienced in top leading banking environment.

anas zeta

anas.zeta
Starting Member

6 Posts

Posted - 2012-08-15 : 06:31:47

quote:
Originally posted by Transact Charlie

Doesn't stop you from accessing the db in other ways - download toad or use sqlcmd..

The best thing to do would be to change the sa password and then put the credentials in a fire proof safe.

Transact Charlie
Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
http://nosqlsolution.blogspot.co.uk/

my client refuse to use any other parties.. and even i used sa user login and not worked..

anas zeta

komkrit
Yak Posting Veteran

60 Posts

Posted - 2012-08-15 : 13:11:39

Dear anas zeta,

You just need modification a little bit my example to match your requirement.
To prohibit newly undefined sysadmin account, use following IF condition

-------------------
IF IS_SRVROLEMEMBER('sysadmin')
ROLLBACK
-------------------

You should specific application name together to narrow scope of prohibition
-------------------
IF IS_SRVROLEMEMBER('sysadmin')
AND APP_NAME() LIKE 'Microsoft SQL Server Management Studio%'
ROLLBACK
-------------------

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Komkrit Yensirikul
Currently be a DBA in worldwide hotel reservation. Experienced in top leading banking environment.

komkrit
Yak Posting Veteran

60 Posts

Posted - 2012-08-15 : 13:30:48

Bare in mind that sa can do anything.
If you create trigger, anyway they can overwrite or delete trigger.
Try to separate sensitive data into another instance of SQL and give permission to each login strictly.
If there are too many sa and you can not control others sa,
Try to enable audit for tracking. Or encrypt data by key with password that only you can decrypt them, so others sa can not readable.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Komkrit Yensirikul
Currently be a DBA in worldwide hotel reservation. Experienced in top leading banking environment.

anas.zeta
Starting Member

6 Posts

Posted - 2012-08-16 : 02:57:25

quote:
Originally posted by komkrit

Bare in mind that sa can do anything.
If you create trigger, anyway they can overwrite or delete trigger.
Try to separate sensitive data into another instance of SQL and give permission to each login strictly.
If there are too many sa and you can not control others sa,
Try to enable audit for tracking. Or encrypt data by key with password that only you can decrypt them, so others sa can not readable.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Komkrit Yensirikul
Currently be a DBA in worldwide hotel reservation. Experienced in top leading banking environment.

thnx alot...

anas zeta

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources