Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server Access Restriction


SQL Server Access Restriction

Author
Message
GregoryF
GregoryF
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1014 Visits: 885
Comments posted to this topic are about the item SQL Server Access Restriction

/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!
Gary Varga
Gary Varga
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8362 Visits: 6161
Thanks for this. I really learnt something new here.

BTW, I love the URLs in the comments (do the same thing myself) as it is a great way to document something which is really documented elsewhere.

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!
ianT
ianT
Old Hand
Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)

Group: General Forum Members
Points: 314 Visits: 1299
How well does this scale? If I have thousands of users hitting the server...

If I'm right, the scale issue will very often not be picked up during testing but only appear when it is released to the production environment.
GregoryF
GregoryF
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1014 Visits: 885
As for scaling, I am not sure how well it would handle thousands of logins per second. My best advice would be to add at the begining of the trigger a exit clause that covers 90% of the cases, i.e.

1) the sql server service account logging from the local server
2) the application service service account where HostName in (list of app servers)
3) and/or ApplicationName = '<Application Name>'
4) any member of the sa role (perhaps hardcoding the members of your dba team)

/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!
Tom Garth
Tom Garth
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1013 Visits: 1499
Really, really, really good!

Thanks Greg.

Tom Garth
Vertical Solutions

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers

srinivasreddy4uhyd
srinivasreddy4uhyd
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 1
Thanks a lot Greg. This script is really helpful.

Correct me If I am wrong.

My situation is I want to restrict group of accounts which can login from some applications but not from SSMS / Excel / DBArtisan / .....

As suggested by you the sql service account can be added to the [BlackList] with [RestrictionEnabled] to zero to avoid rollback right ?

Please Advise.

Thanks & Regards,
Srini
GregoryF
GregoryF
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1014 Visits: 885
srinivasreddy4uhyd (3/11/2010)
Thanks a lot Greg. This script is really helpful.

Correct me If I am wrong.

My situation is I want to restrict group of accounts which can login from some applications but not from SSMS / Excel / DBArtisan / .....

As suggested by you the sql service account can be added to the [BlackList] with [RestrictionEnabled] to zero to avoid rollback right ?

Please Advise.

Thanks & Regards,
Srini




Section #6 of the Trigger covers this scenario:

    --#6
    --If a particular application connects to SQL Server, with a given UserName (i.e. service account cannot connect with SSMS)
    If(Exists(Select * from SQL_Audit.dbo.BlackList where AppName = @AppName and LoginName = @User and RestrictionEnabled = 1))

To set it up, insert to the blacklist table

User and application

Each user and application requires a seperate entry, i.e.

user1, Excel
user1, Access
user1, Toad
user2, Excel
user2, Access
user2, Toad

So update the table with each developer login and the application that cannot be used.

Alternatively, you could add a condition at the begining of the trigger:

If ((@User in ('User1', User2', 'User3')) and (@AppName in ('Excel', Access', 'TOAD')))
Begin
Rollback

  insert into SQL_Audit..Violations
                (PostDate, LoginName, IPAddress, HostName, ServerName, AppName, ViolationType)
                values (@PostTime, @User, @IPAddress, @HostName, @SrvName, @AppName, 'ApplicationName')


Return;

End

The advantage of the second method is that the hardcoded values require less disk IO to process; however, the disadvantage is that the SP would have to be recreated to every change in Users and apps.

/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!
atul.verma.ietk
atul.verma.ietk
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 27
Hi,

while using this script I had created sql_audit database but not able to create trigger getting following error while running provided scrip

Msg 156, Level 15, State 1, Procedure trg_LoginBlackList, Line 5
Incorrect syntax near the keyword 'as'.
Msg 1088, Level 16, State 119, Line 2
Cannot find the object "trg_LoginBlackList" because it does not exist or you do not have permissions.

Please revert ..........
atul.verma.ietk
atul.verma.ietk
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 27
any one plz help on this
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search