x

Prevent Sa user from remote login

Hi all i have a query regarding login authentication.

Is there any way to prevent sa user from remote login other than WINDOW AUTHENTICATION AND ACTIVE DIRECTORY AUTHENTICATIONS?

How can we prevent sa user from remote login using SSMS?

-- Krishnakant Joshi
more ▼

asked Dec 20, 2011 at 03:51 AM in Default

krishnakantj gravatar image

krishnakantj
0 1 1 1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

I think you should specify the reason why you want this. Then we could lead you to a better path.

As far as how it could be done is, At the moment I can only think of a DDL LOGON TRIGGER as the solution

You could try something like this in the LOGON TRIGGER

IF SUSER_SNAME() = 'sa'
BEGIN
IF HOST_NAME() <> 'YOURSERVERNAME'
ROLLBACK
END

OR

DECLARE @Ipadress VARCHAR(48)
IF SUSER_SNAME() = 'sa'
BEGIN
        SELECT  @Ipadress = [client_net_address]
        FROM    sys.[dm_exec_connections]
        WHERE   [session_id] = @@SPID

       IF @Ipadress NOT IN ( '<local machine>', 'YOURSERVERIP') 
            ROLLBACK
END
Hope it will help your cause, but please do let us know why you want it.
more ▼

answered Dec 20, 2011 at 05:42 AM

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

+1 here's more on logon triggers

http://technet.microsoft.com/dd898898.aspx
Dec 20, 2011 at 10:25 AM Scot Hauder

Hi Usman,

Thanks for the reply. You have solved my query. There is no specific reason behind this question. One day i was just reading an article about authentication in SQL server which raised this question. :) Thanks Again
Dec 27, 2011 at 11:26 PM krishnakantj
(comments are locked)
10|1200 characters needed characters left

Your best solution to prevent remote login by the 'sa' account is to change the password and stop letting people use it. Security wise, this account should not be used for day-to-day activity.

If access is required by a user or application, a dedicated account should be created so you know who is doing what in your system.

more ▼

answered Dec 20, 2011 at 08:43 AM

Shawn_Melton gravatar image

Shawn_Melton
5.4k 20 21 29

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x86
x5

asked: Dec 20, 2011 at 03:51 AM

Seen: 1327 times

Last Updated: Dec 20, 2011 at 03:51 AM