Securing Application Account

  • Hello Fellas, I have one Application account dedicated for my application and just want to ensure no user uses it. I need it with mixed authenticaiton as it needs to connect non windows environment as well. wanted to know if I can ensure that no one uses this account apart from the application.

  • You could use a logon trigger:

    CREATE TRIGGER [TR_LOGON_MyApplication]

    ON ALL SERVER

    FOR LOGON

    AS

    BEGIN

    DECLARE @program_name nvarchar(128)

    DECLARE @host_name nvarchar(128)

    SELECT @program_name = program_name,

    @host_name = host_name

    FROM sys.dm_exec_sessions AS c

    WHERE c.session_id = @@spid

    IF ORIGINAL_LOGIN() = 'MyAppLogin'

    AND @program_name <> 'MyApplicationName'

    BEGIN

    RAISERROR('MyAppLogin cannot be used by interactive users.',16,1)

    ROLLBACK;

    END

    END;

    Whenever you connect to the server using MyAppLogin using, for instance, SSMS, you will get an error.

    Be extremely careful with logon triggers: you could end up being locked out of your instance!!! Test thoroughly!!!

    Hope this helps

    Gianluca

    -- Gianluca Sartori

  • Thanks. However Isn't there a way to encrypt the password ?

  • I think I don't understand your question. Can you clarify?

    This is a totally different issue, isn't it?

    Where exactly you want to encrypt the password? On the app side?

    The password is *ALWAYS* stored encrypted in SQL Server and there is no way to read it from the server storage.

    -- Gianluca Sartori

  • Gianluca, sorry for the confusion. Seems your solution is best way to protect mixed mode authenticated application account.

  • Well, I don't know if this is the best way... for sure it is one way.

    I can only say that I had to do the same thing some years ago and it worked for me.

    Glad I could help

    Gianluca

    -- Gianluca Sartori

  • Well issue may occur where a logon trigger, during periods of domain controller latency, can prevent access due to the security check.

Viewing 7 posts - 1 through 6 (of 6 total)

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