Sql-server – SQL User Security

sql-server-2008

We have applications connected to SQL using windows authentication. While having connection with Application user can also access to Database instance on the same time as well. We need to limit the access of user outside application. How we can limit user to access DB from application but not outside application?

Best Answer

The first thing that comes to mind is a logon trigger. But that's not really the right answer. The right answer is to give the logins (your Windows users) only the permissions they require. An application is not a security layer and you shouldn't be treating it as such.

In other words, if the users are able to SELECT and INSERT into dbo.Table1, then they only need those permissions. Don't make them members of the sysadmin fixed server role in the hopes they only hit the database through the application.

Related Solutions

Sql-server – Using integrated security over VPN

If the client computer isn't on the same domain as the SQL Server, and if there's no trusts set up between the client computer's domain and the SQL Server's domain, you can't use integrated authentication.

Sql-server – Local user login access in sql server 2008

To figure out exactly what is happening, you should check the State that is reported in the message, either in the event log or SQL Server log. For example:

Error: 18456, Severity: 14, State: 8.

The state will usually help narrow down the exact cause. Here is a list of the states and their meanings:

Err  Message
 2   Invalid userid 
 5   Invalid userid
 6   Attempt to use a Windows login name with SQL Authentication
 7   Login disabled or password mismatch
 8   Password mismatch
 9   Invalid password
11   Valid login but server access failure
12   Valid login but server access failure
13   SQL Server service paused
18   Change password required

Best Answer

Related Solutions

Sql-server – Using integrated security over VPN

Sql-server – Local user login access in sql server 2008

Related Question