Limit connection rights on SQL Server

I have an app to deploy in production that uses 'honor system' security. That is, all users connect to the DB using a SQL user/passwd credential and the app manages permissions itself. The latter part doesn't bother me as much as the fact that the connection object contains embedded credentials and can be copied around freely. I'm try to find some way to limit connections to a more limited set of clients. I can create firewall rules to limit by IP, of course. Is there any way to 'prequalify' SQL logins either by Machine account or domain membership?

As Thomas mentioned that it can be done using LOGON Trigger. Below is the script that will help you out

```
/*

http://www.sqlservercentral.com/scripts/Security/69558/
Credit: Gregory A. Ferdinandsen
--greg@ferdinandsen.com
--Revision 1.0, 8 Feb 10
--Requires SQL 2005 SP2 or higher
*/
if not exists (select 1 from master..sysdatabases where name = 'SQL_Audit')
begin
create database SQL_Audit
end
USE [SQL_Audit]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].BlackList NOT NULL,
[HostName] varchar NULL,
[IP_Address] varchar NULL,
[LoginName] varchar NULL,
[AppName] varchar NULL,
[RestrictionEnabled] [bit] NULL,
[Description] varchar NULL,
CONSTRAINT [PK_BlackList] PRIMARY KEY CLUSTERED
(
[SRV_Rule] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[BlackList] ADD CONSTRAINT [DF_BlackList_RestrictionEnabled] DEFAULT ((0)) FOR [RestrictionEnabled]
GO

---------------------------------------------------------------------
---------------------------------------------------------------------
USE [SQL_Audit]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].Violations NOT NULL,
[PostDate] [datetime] NOT NULL,
[LoginName] varchar NULL,
[IPAddress] varchar NULL,
[HostName] nvarchar NULL,
[ServerName] varchar NULL,
[AppName] nvarchar NULL,
[ViolationType] varchar NULL,
CONSTRAINT [PK_Violations] PRIMARY KEY CLUSTERED
(
[ViolationNum] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Violations] ADD CONSTRAINT [DF_Violations_PostDate] DEFAULT (getdate()) FOR [PostDate]
GO
---------------------------------------------------------------------
---------------------------------------------------------------------
--(c) Gregory A. Ferdinandsen
--greg@ferdinandsen.com
--Revision 1.0, 8 Feb 10
--Requires SQL 2005 SP2 or higher

--
--Change with > for a valid service account that has sa rights
--
--Information on Logon Triggers: http://msdn.microsoft.com/en-us/library/bb326598.aspx
--
USE Master
go

CREATE Trigger [trg_LoginBlackList]
on all Server

as
begin

declare @data XML
declare @User as varchar(128)
declare @HostName as varchar(64)
declare @IPAddress as varchar(15)
declare @AppName as nvarchar(256)
declare @SPID as int
declare @SrvName as nvarchar(96)
declare @PostTime as datetime
declare @LogMsg as varchar(1024)

set @data = EVENTDATA()
set @User = @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(128)')
set @IPAddress = @data.value('(/EVENT_INSTANCE/ClientHost)[1]', 'nvarchar(15)')
set @SPID = @data.value('(/EVENT_INSTANCE/SPID)[1]', 'int')
set @SrvName = @data.value('(/EVENT_INSTANCE/ServerName)[1]', 'nvarchar(96)')
set @PostTime = @data.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime')
set @HostName = Cast(Host_Name() as nvarchar(64))
set @AppName = Cast(App_Name() as nvarchar(256))

--Check to see if the blacklist table exists, if the table does not exist, exit the Trigger, as otherwise all user would be locked out.

if Not Exists (select * from SQL_Audit.INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'BlackList')
begin
return;
end

--#1
--If a user connects from a given work station and with a given UserName, they will be dissconected
--This user need to be set up in SQL_Audit..Blacklist with a user name and a host name, no IP Address is necesary
--This is the prefered method of blacklisting, as DHCP could reak havoc on any IP restrictions
If(Exists(Select * from SQL_Audit.dbo.BlackList where LoginName = @User and HostName = @HostName and RestrictionEnabled = 1))
begin
--Any data modifications made up to the point of ROLLBACK TRANSACTION are rolled back
--The current trigger continues to execute any remaining statements that appear after the ROLLBACK statement.
--If any of these statements modify data, the modifications are not rolled back.
--http://technet.microsoft.com/en-us/library/bb153915.aspx
rollback

insert into SQL_Audit..Violations
(PostDate, LoginName, IPAddress, HostName, ServerName, AppName, ViolationType)
values (@PostTime, @User, @IPAddress, @HostName, @SrvName, @AppName, 'LoginName, HostName')

--Exit trigger without evaluating any further conditions
return;
end

--#2
--If a user connects from a given IP Address and with a given UserName, they will be dissconected
--This user need to be set up in SQL_Audit..Blacklist with a user name and a IP Address, no HostName is necesary
If(Exists(Select * from SQL_Audit.dbo.BlackList where LoginName = @User and IP_Address = @IPAddress and RestrictionEnabled = 1))
begin
--Any data modificati

StackExchange Database Administrators Q#37379, answer score: 12