How to prevent user login to SQL Management Studio ?

Question

How to prevent user login to SQL Management Studio ?

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

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

Lowell SSC Guru Points: 323504 More actions · Answer 1

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!

spaghettidba SSC Guru Points: 105732 More actions · Answer 2

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 6

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

dallas13 SSCertifiable Points: 7491 More actions · Answer 7

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.

spaghettidba SSC Guru Points: 105732 More actions · Answer 8

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

-- Gianluca Sartori

dallas13 SSCertifiable Points: 7491 More actions · Answer 9

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?

Lowell SSC Guru Points: 323504 More actions · Answer 10

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!

dallas13 SSCertifiable Points: 7491 More actions · Answer 11

I tried it but its giving me error.

ALTER TRIGGER [TR_LOGON_APPUSER_SSMS]

EXECUTE AS OWNER

Lowell SSC Guru Points: 323504 More actions · Answer 12

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!

dallas13 SSCertifiable Points: 7491 More actions · Answer 13

dallas13

SSCertifiable

Points: 7491

December 3, 2015 at 12:31 pm

#1843865

Thanks but still its throwing the error message.

Lowell SSC Guru Points: 323504 More actions · Answer 14

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!

Sean Lange SSC Guru Points: 286573 More actions · Answer 15

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/