How to prevent user login to SQL Management Studio ?

  • but the logon trigger only works if the person doesn't know how to change their connection string to explicitly say an application name:

    so the logon trigger is not 100% realiable, it will on discourage non determined users:

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Lowell (1/16/2012)


    but the logon trigger only works if the person doesn't know how to change their connection string to explicitly say an application name:

    so the logon trigger is not 100% realiable, it will on discourage non determined users:

    Agreed.

    In fact, I think that an application role with a strong password would be a better solution.

    However, I had to do this because there was no way to change the application, users knew app credentials and were not smart enough to change the connection string.

    It's a sort of "security by obscurity" or "false security", but seemed to work at the time.

    Another possible solution could be allowing connections from the application/web server only for this login. Again, would not be a proper security measure.

    -- Gianluca Sartori

  • You also need to watch out for maintenance plans which, iirc, get an application name of SQL Server Management Studio.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • I think the better method is to use a firewall. Through the firewall, put explicit permissions for IP addresses to access the Database Zone. This doesn't prevent somebody from trying to use SSMS on the app server, but does prevent it from their desktops unless they are supposed to have access.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Another option, especially if there's a middle-tier for the application, is for the app to impersonate a different windows login for connections to the DB. That way, the users don't have any permission on the DB at all. Not the easiest thing to implement though.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (1/16/2012)


    Another option, especially if there's a middle-tier for the application, is for the app to impersonate a different windows login for connections to the DB. That way, the users don't have any permission on the DB at all. Not the easiest thing to implement though.

    Good point. It think it would be the same with SQL authentication, as it basically falls into the same "don't give out credentials" category.

    -- Gianluca Sartori

  • This is a good solution. Thanks but when I tried it in SQL 2014,

    I did not see error message that we have supplied in trigger, instead I m getting this,

    "Logon failed for login 'test' due to trigger execution.

    Changed database context to 'ReportServer'.

    Changed language setting to us_english. (.Net SqlClient Data Provider)"

    I need to display correct error message. any suggestions?

    Thanks again.

  • Sorry, it's not possible. You have no control over the error message displayed at the client.

    -- Gianluca Sartori

  • Hi Instead of throwing error message from script can I send email only to dba saying this user tried to logon from ssms? Dont throw error and allow them to login but send me an email.

    --SELECT * FROM sys.dm_exec_sessions order by login_time desc

    USE [master]

    GO

    /****** Object: DdlTrigger [TR_LOGON_APP] Script Date: 12/3/2015 11:56:37 AM ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    ALTER TRIGGER [TR_LOGON_APPUSER_SSMS]

    ON ALL SERVER

    FOR LOGON

    AS

    BEGIN

    DECLARE @program_name nvarchar(128)

    DECLARE @host_name nvarchar(128)

    DECLARE @login_name nvarchar(128)

    DECLARE @login_time DATETIME

    DECLARE @Subject_line nvarchar(128)

    DECLARE @body_line nvarchar(MAX)

    SELECT @program_name = program_name,

    @host_name = host_name,

    @login_name = login_name,

    @login_time = login_time

    FROM sys.dm_exec_sessions AS c

    WHERE c.session_id = @@spid

    IF ORIGINAL_LOGIN() LIKE '%user' OR ORIGINAL_LOGIN() LIKE 'ssis%' OR ORIGINAL_LOGIN() LIKE 'bo%'

    AND @program_name LIKE '%Management%Studio%'

    BEGIN

    --RAISERROR('This login is for application use only.',16,1)

    SET @Subject_line = 'Application user tried to login to ssms on '+@host_name+' Detected'

    SET @body_line = 'Application user ' + @login_name+ ' tried to login to SSMS from ' + @host_name+ ' from '+ @program_name+ ' at ' + @login_time +'.'

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'dev',

    @recipients = 'abc@abc.com',

    @subject = @Subject_line,

    @body = @body_line

    --ROLLBACK;

    END

    END;

    GO

    I tried something like this but still its throwing error message while loging.

    Any suggestions?

  • i think you'll need EXECUTE AS OWNER in the trigger; otherwise it's the end users context, i think, who might not have access to msdb, and thus the ability to send mail.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • I tried it but its giving me error.

    ALTER TRIGGER [TR_LOGON_APPUSER_SSMS]

    EXECUTE AS OWNER

  • dallas13 (12/3/2015)


    I tried it but its giving me error.

    ALTER TRIGGER [TR_LOGON_APPUSER_SSMS]

    EXECUTE AS OWNER

    i have a couple of model DDL triggers that execute as specific users; i think that's the way to go

    CREATE TRIGGER [Logon_Trigger_Monitor_Excel]

    ON ALL SERVER WITH EXECUTE AS 'HERMES\SERVICEACT'

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Thanks but still its throwing the error message.

  • dallas13 (12/3/2015)


    Thanks but still its throwing the error message.

    what specific error do you get? also look in the SQL error log for a detailed error instead of an "ended in a trigger " kind of error.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Lowell (12/3/2015)


    dallas13 (12/3/2015)


    I tried it but its giving me error.

    ALTER TRIGGER [TR_LOGON_APPUSER_SSMS]

    EXECUTE AS OWNER

    i have a couple of model DDL triggers that execute as specific users; i think that's the way to go

    CREATE TRIGGER [Logon_Trigger_Monitor_Excel]

    ON ALL SERVER WITH EXECUTE AS 'HERMES\SERVICEACT'

    Got bitten by the quote bug. 🙂 Lowell beat me to it.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Viewing 15 posts - 16 through 30 (of 33 total)

You must be logged in to reply to this topic. Login to reply