January 19, 2010 at 3:28 pm
We have user names for applications that have higher security than we want our developers to have. However, the developers need the pwd to put into their apps to connect. Is there a way to restrict a login from SQL Server Management Studio only, but can still connect to do what is needed?
Thanks!
January 19, 2010 at 4:07 pm
I don't think there is a way to retrict the source of a login, but you can set up a SQL trace so that you can monitor how and where that user account logs into the database instance. From the trace information, you could detmine if the account is being used by a developer to log in via SSMS. You should be able to determine which workstation logged in with that account and tie that back to a specific developer.
January 19, 2010 at 5:33 pm
checkai (1/19/2010)
We have user names for applications that have higher security than we want our developers to have. However, the developers need the pwd to put into their apps to connect. Is there a way to restrict a login from SQL Server Management Studio only, but can still connect to do what is needed?Thanks!
i'm not sure if you want to prevent your developers from using SSMS or you want to limit them to ONLY use SSMS.
either way, it is possible by using a logon trigger in the database....but you have to be careful!
if you made a logon trigger that did not permit SSMS, you yourself could be prevented from connecting!
here's an example below;i've modified the original "prevent any excel connections" example this is designed to prevent specific users from using excel. from there it's kind of intuitive to change it around to suit your needs.
try this on atest server before you ever put it into place, make sure you've got it working the way you want without locking yourself out.
-- THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF
-- ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO
-- THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
-- PARTICULAR PURPOSE.
-- Frederik Vandeputte - http://www.vandeputte.org
--TESTED ON SQL 2005 SP2 CTP
USE MASTER
GO
--Clean up old logging db
IF DB_ID ('Logging_demoSP2') IS NOT NULL
BEGIN
ALTER DATABASE Logging_demoSP2 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE Logging_demoSP2
END
GO
--Create Logging DB and table
CREATE DATABASE Logging_demoSP2
GO
USE Logging_demoSP2
CREATE TABLE Logging (
LoggingID int IDENTITY(1,1),
EventTime DATETIME,
EventType VARCHAR(100),
LoginName VARCHAR(100),
HostName VARCHAR(100),
AppName VARCHAR(255),
FullLog XML
)
GO
--The LOGON Trigger
CREATE TRIGGER logon_trigger
ON ALL SERVER
FOR LOGON
AS
BEGIN
DECLARE @message_body XML
SET @message_body = eventdata()
INSERT INTO Logging_demoSP2.dbo.Logging (
EventTime,
EventType,
LoginName,
HostName,
AppName,
FullLog )
VALUES
(
CAST(CAST(@message_body.query('/EVENT_INSTANCE/PostTime/text()') AS VARCHAR(64)) AS DATETIME),
CAST(@message_body.query('/EVENT_INSTANCE/EventType/text()') AS VARCHAR(100)),
CAST(@message_body.query('/EVENT_INSTANCE/LoginName/text()') AS VARCHAR(100)),
CAST(@message_body.query('/EVENT_INSTANCE/ClientHost/text()') AS VARCHAR(100)),
APP_NAME(),
@message_body)
END
GO
--Open demo connections
select * from logging
--Prevent access from Excel by specific users
CREATE TRIGGER logon_trigger_not_from_excel
ON ALL SERVER FOR LOGON
AS
BEGIN
IF APP_NAME() LIKE '%excel%' --or SQL Server Management Studio or Access or yourApp
--user_name() AS [user_name],
--suser_name() AS [suser_name],
--current_user AS [current_user],
--system_user AS [system_user],
--session_user AS [session_user],
--user AS
AND lower(user_name()) IN ('webdev','bob','tom','kathy')
ROLLBACK
END
--Try to connect from Excel
--Clean DB
USE MASTER
GO
ALTER DATABASE Logging_demoSP2 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE Logging_demoSP2
DROP TRIGGER logon_trigger ON ALL SERVER
DROP TRIGGER logon_trigger_not_from_excel ON ALL SERVER
Lowell
January 20, 2010 at 6:12 am
Thanks for the starters...basically I only want them to use SSMS using their Windows credentials instead of they application logins that have greater rights.
January 20, 2010 at 6:37 am
gotcha...so you want to kill their connection if they use your the logon designated for your application and SQL Server Management Studio(or anything except your application:
the if statement would look like this:
IF lower(user_name()) IN ('myapplogin') --this logon restricted to one specific app_name:
AND APP_NAME() NOT LIKE '%Your Application Name%'
ROLLBACK
this isn't bulletproof, however, because the application name can be included in the connection string; I can build my own application and have it any application string i want:
Dim SqlTrustedConnectionFormat As String = "data source={0};initial catalog={1};Trusted_Connection=True;Application Name=Your Application Name;"
Lowell
January 20, 2010 at 8:13 am
Good info Lowell. I had wondered if that could be done with triggers and I looked into that before I posted my reply. I looked at the DDL triggers and realized that it would not work like I wanted. For some reason, Logon triggers escaped me. Again, good info.
January 20, 2010 at 3:08 pm
Small addon: I use logon triggers to use the source address as filtering criteria. To avoid any tampering opportunity, this provides the IP address of the client, no name resolution. So you can say that connection for the stronger accounts is possible only from the application servers.
-- Erik http://blog.rollback.hu
January 20, 2010 at 4:31 pm
Erik can you give a code example? I've recently found the IP address like this whenever i needed it:
select client_net_address from sys.dm_exec_connections where session_id = @@spid,
if there is another way, I'd like to add that to my snippets.
Lowell
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply