July 7, 2011 at 1:57 am
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.
July 7, 2011 at 2:35 am
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
July 7, 2011 at 3:03 am
Thanks. However Isn't there a way to encrypt the password ?
July 7, 2011 at 3:10 am
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
July 7, 2011 at 5:25 am
Gianluca, sorry for the confusion. Seems your solution is best way to protect mixed mode authenticated application account.
July 7, 2011 at 5:30 am
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
July 7, 2011 at 7:32 am
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