Secure SQL Server remote access from ISV application

SQL Server Security

-=B3N=-


Hi guys

I'm writing a C# application which connects to a local SQL database for data access. The application connects to SQL Server through windows authentication, but opens up the port and sqlbrowser to others on the network wanting to access the database through SQL Server authentication, and also allows remote users to connect to this server remotely if they have the login and password (and because the port is already open)

I understand this is not secure and open to attack, and am unsure of how to secure these processes without blocking these three types of access, from A.) the local user, B.) the network user and C.) the remote user across the net.

Have researched this a fair bit, but get somewhat lost amongst all the jargon.

Top

Re: Secure SQL Server remote access from ISV application

mrdenny


If all access to the database is supose to be from the local user only you can tell Microsoft SQL Server to not accept network connections.

Code Snippet

exec sp_configure 'remote access', 0

reconfigure

Then restart the SQL Server. This will block all network users either on the local lan or the internet.

There is no way to block the local user from connecting while still allowing your application to connect.






Top

Re: Secure SQL Server remote access from ISV application

bmcneill0

In that case, would i be best to create some sort of "service" as a middleman between the client application and the SQL Server instance, whereby the SQL select queries for example can be sent from the client app through the service to SQL Server, and the result back through the service to the application, and set the service to allow requests only from my app, and block the SQL public port so SQL Server can only be accessed through the local service, but have the service able to be accessed remotely



Top

Re: Secure SQL Server remote access from ISV application

Il-Sung Lee - MSFT

Hi,

What is your main concern That a port is opened in the firewall for sqlbrowser.exe If so, then be aware that you can close he port, disable SQL Server Browser and still be able to connect to the database. SQL Server Browser is not used to facilitate SQL Auth connections. Rather it provides endpoint information, like TCP/IP port number, pipe name, etc., of a named instance to a client when trying to connect to the server. Specifically, when the server is specified as "server\instance". If you specify the port or named pipe name in the connection string, e.g., "server,port" then the client does not need to contact SQL Browser.

Check out this link for more info about SQL Sever Browser:

http://msdn2.microsoft.com/en-us/library/ms181087.aspx

Il-Sung.





Top

Re: Secure SQL Server remote access from ISV application

Guennadii Vanine

mrdenny wrote:

There is no way to block the local user from connecting while still allowing your application to connect.


I believe that it is possible to restrict users to login only through application with the use of application roles (sp_setapprole) [1]. Search BOL, internet for more articles on the issue

[1]

SQL Server 2005 BOL

Application Roles

http://msdn2.microsoft.com/en-us/library/ms190998.aspx

Added later:
Check also my answer in
http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=1612977&SiteID=1&mode=1






Top