Preventing Remote Access to SQL Server

Our company's customer wants to prevent anyone from logging into SQL Server unless they are actually logged on the server machine. (In other words they do not want a user to be able to connect unless he or she is sitting at the server's keyboard). They do not wish to utilize NT authenication. In addition, there are a number of ASP and MTS apps that are required to connect using ADO connections. These apps are located on another server which is acting as the Web Server and the MTS Server. This server is on the other side of a firewall. I have been searching the Web for weeks now and have been unable to find any reference to anything similar. Any advice would be most appreciated.

JLLR
MCDBA MCSD
Database DeveloperThere is "a way" to prevent logging into the server unless sitting in front of it, but how do you picture those apps to continue functioning? Can you clarify your question?|||Here's the deal. SQL Server is running a machine we will call DBserver. There are several Web apps, and MTS apps running on another machine which is acting as an MTS Server and a Web Server. We will call this machine WBServer. There are also some VB exe's running on some client machines in the Network. WBServer is separated from DBServer by a firewall, (with WB server being outside the firewall). The servers are located in the server room. Inside the firewall, the are several machines which could connect to SQL Server using client tools (enterprise manager, Query analyzer, etc.) In addition, there were some machines linking to the data using ODBC connections in MS Access. Due to the fact there were alterations to the data in the tables which caused major complications to the National Defense readiness, the customer now requires all ad hoc connections to SQL Server be done on DBServer after the person making the change lsigns a logbook recording their entry into the server room and which machine on to which they will be logging. Due to the fact that individuals are violating the rules they wish to set up SQL Server to make it impossible to make an ad hoc connection other than from DBServer. However they do not want affect the way the various applications work. The obvious answer was to provide a limited number of logins and restrict their distribution. However, the passwords did not remain secret, and persons were able to get them and login. I have some thoughts bof a work around but I would like something better.

What do you think.|||If you wish to prevent users from connecting to SQL Server using Microsoft SQL Client Tools, if the are not sitting at the keyboard of the server. You could look at creating a script (job) that runs every 10 seconds let's say and looks for records in SYSPROCESSES for connections where program_name is in a list example :
SQL Query Analyzer
SQL Query Analyzer - Object Browser
SQL Profiler
ISQL-32
etc. and hostname is not equal to DBserver (using your name). Any matches would have there process terminated.

PS

What about users that create a link to SQL Server via MS Access and then perform ad-hoc queries via MS Access?|||Also Forbidden.|||Very interesting. There is the problem of server resources, and how often to run this procedure.. A user can do a lot of damage in 10 seconds. I will think about it.|||Actually a KILL is not forbidden, if you thought I meant to "DELETE" the record from sysprocesses your mistaking. As far as the 10 seconds goes that is an arbitrary number.

To correctly do what you want, you would not allow direct access to the database tables in the first place.

At our site all data queries and modifications are done via stored procedures which is in a controlled environment. Any ad-hoc queries are done on a separate server which is refreshed via database backup from production on a nightly bases.|||You misunderstand. MS Access ODBC links to the server are also forbidden.

It sounds like DBA's have some authority in your shop. Here they are just glorified data entry people until something goes wrong, then they do not listen to the advice anyway, or they ask you to build the impossible. If it were up to me, all apps would connect through Application roles., and no one would be able to connect to the DB if they were not in the an administrators role Then only certain NT Logins would be in that role. (Can't make that Admin group of Windows NT because everyone is in Admin group!!) But what do I know I am only an MCDBA.|||Tell them they can't screen their doors with chicken wire and then complain about flies in the house.

blindman|||There is a way to allow only certain applications to access a database on a SQLServer. It's called APPLICATION ROLE. This is the article in the Books on Line for it:

Establishing Application Security and Application Roles
The security system in Microsoft SQL Server is implemented at the lowest level: the database itself. This is the best method for controlling user activities regardless of the application used to communicate with SQL Server. However, sometimes security controls must be customized to accommodate the special requirements of an individual application, especially when dealing with complex databases and databases with large tables.

Additionally, you may want users to be restricted to accessing data only through a specific application (for example using SQL Query Analyzer or Microsoft Excel) or to be prevented from accessing data directly. Restricting user access in this way prohibits users from connecting to an instance of SQL Server using an application such as SQL Query Analyzer and executing a poorly written query, which can negatively affect the performance of the whole server.

SQL Server accommodates these needs through the use of application roles. Application roles are different than standard roles in that:

Application roles contain no members.
Microsoft Windows NT 4.0 or Windows 2000 groups, users, and roles cannot be added to application roles; the permissions of the application role are gained when the application role is activated for the user's connection through a specific application or applications. A user's association with an application role is due to his ability to run an application that activates the role, rather than his being a member of the role.

Application roles are inactive by default and require a password to be activated.

Application roles bypass standard permissions.
When an application role is activated for a connection by the application, the connection permanently loses all permissions applied to the login, user account, or other groups or database roles in all databases for the duration of the connection. The connection gains the permissions associated with the application role for the database in which the application role exists. Because application roles are applicable only to the database in which they exist, the connection can gain access to another database only through permissions granted to the guest user account in the other database. Therefore, if the guest user account does not exist in a database, the connection cannot gain access to that database. If the guest user account does exist in the database but permissions to access an object are not explicitly granted to guest, the connection cannot access that object, regardless of who created the object. The permissions the user gained from the application role remain in effect until the connection logs out of an instance of SQL Server.

To ensure that all the functions of the application can be performed, a connection must lose default permissions applied to the login and user account or other groups or database roles in all databases for the duration of the connection and gain the permissions associated with the application role. For example, if a user is usually denied access to a table that the application must access, then the denied access should be revoked so the user can use the application successfully. Application roles overcome any conflicts with user's default permissions by temporarily suspending the user's default permissions and assigning them only the permissions of the application role.

Application roles allow the application, rather than SQL Server, to take over the responsibility of user authentication. However, because SQL Server still must authenticate the application when it accesses databases, the application must provide a password because there is no other way to authenticate an application.

If ad hoc access to a database is not required, users and Windows NT 4.0 or Windows 2000 groups do not need to be granted any permissions because all permissions can be assigned by the applications they use to access the database. In such an environment, standardizing on one system-wide password assigned to an application role is possible, assuming access to the applications is secure.

There are several options for managing application role passwords without hard-coding them into applications. For example, an encrypted key stored in the registry (or a SQL Server database), for which only the application has the decryption code, can be used. The application reads the key, decrypts it, and uses the value to set the application role. Using the Multiprotocol Net-Library, the network packet containing the password can also be encrypted. Additionally, the password can be encrypted, before being sent to an instance of SQL Server, when the role is activated.

When an application user connects to an instance of SQL Server using Windows Authentication Mode, an application role can be used to set the permissions the Windows NT 4.0 or Windows 2000 user has in a database when using the application. This method allows Windows NT 4.0 or Windows 2000 auditing of the user account and control over user permissions, while she uses the application, to be easily maintained.

If SQL Server Authentication is used and auditing user access in the database is not required, it can be easier for the application to connect to an instance of SQL Server using a predefined SQL Server login. For example, an order entry application authenticates users running the application itself, and then connects to an instance of SQL Server using the same OrderEntry login. All connections use the same login, and relevant permissions are granted to this login.

Note Application roles work with both authentication modes.

Example
As an example of application role usage, a user Sue runs a sales application that requires SELECT, UPDATE, and INSERT permissions on the Products and Orders tables in database Sales to work, but she should not have any SELECT, INSERT, or UPDATE permissions when accessing the Products or Orders tables using SQL Query Analyzer or any other tool. To ensure this, create one user-database role that denies SELECT, INSERT, or UPDATE permissions on the Products and Orders tables, and add Sue as a member of that database role. Then create an application role in the Sales database with SELECT, INSERT, and UPDATE permissions on the Products and Orders tables. When the application runs, it provides the password to activate the application role by using sp_setapprole, and gains the permissions to access the Products and Orders tables. If Sue tries to log in to an instance of SQL Server using any tool except the application, she will not be able to access the Products or Orders tables.

To create an application role

Transact-SQL

Enterprise Manager

How to create an application role (Enterprise Manager)
To create an application role

Expand a server group, and then expand a server.

Expand Databases, and then expand the database in which to create a role.

Right-click Roles, and then click New Database Role.

In the Name box, enter the name of the new application role.

Under Database role type, click Application role, and then enter a password.

See Also

Establishing Application Security and Application Roles

SQL-DMO

To set an application role

Transact-SQL

To change the password of an application role

Transact-SQL

SQL-DMO

To remove an application role

Transact-SQL

Enterprise Manager

How to remove an application role (Enterprise Manager)
To remove an application role

Expand a server group, and then expand a server.

Expand Databases, and then expand the database in which the application role exists.

Click Roles.

In the details pane, right-click the application role to remove, and then click Delete.

Confirm the deletion.

See Also

Establishing Application Security and Application Roles

SQL-DMO


For more information search the web with google and ask for application role sql server

Good luck
ionut

Originally posted by Joeller
Our company's customer wants to prevent anyone from logging into SQL Server unless they are actually logged on the server machine. (In other words they do not want a user to be able to connect unless he or she is sitting at the server's keyboard). They do not wish to utilize NT authenication. In addition, there are a number of ASP and MTS apps that are required to connect using ADO connections. These apps are located on another server which is acting as the Web Server and the MTS Server. This server is on the other side of a firewall. I have been searching the Web for weeks now and have been unable to find any reference to anything similar. Any advice would be most appreciated.

JLLR
MCDBA MCSD
Database Developer|||to inut: As stated earlier that is what I would do if I were able to do what I want. But I can't and that's all there is to that.

To blindman: It would be nice to tell these people what I think of their office politics, their so-called back up plans, their horrific excuse for a database etc., but that would probably cost our company it major customer and put 3/4 of the people here out of work. So I smile and tell them I will investigate ways to do what they want. It appears that the suggestion earlier re Killing the process after it starts will end up ing the only way to do this, as that is the third time I have gotten this suggestion. Then maybe I can put forward my plan for role based security. sigh.|||...but make sure it isn't YOUR head that rolls when (not if) the house of cards collapses.

blindman