Need Help to configure restricted Access to SQL Servers in a Network.

  • Dear All,

    I need to restrict access to my 3 SQL Servers databases installed in 3 different server machines. I want these machines act as linked servers, but I don't want any of these databases to attach in SQL management studio. Also, I don't want to access the SQL Server data using applications from remote machines,(but using the linked server option).

    Right now, I am trying using LOGON trigger. But what I actually want is a permanent solution as the LOGON trigger needs manually handles the authentication.

    Thanks in Advance.

    Bala G

  • You can use following with 2 Cautions:

    •OPENROWSET / OPENDATASOURCE can be used to access remote data from OLE DB data sources only when the DisallowAdhocAccess registry option is explicitly set to 0 for the specified provider, and the Ad Hoc Distributed Queries advanced configuration option is enabled. When these options are not set, the default behavior does not allow for ad hoc access.

    •This method is an alternative to accessing tables in a linked server and is a one-time, ad hoc method of connecting and accessing remote data by using OLE DB. For more frequent references to OLE DB data sources, use linked servers instead.

    OPENROWSET (Transact-SQL)

    http://msdn.microsoft.com/en-us/library/ms190312.aspx

    OPENDATASOURCE (Transact-SQL)

    http://msdn.microsoft.com/en-us/library/ms179856.aspx

  • Even tough I don't like what you're about to do.

    ( only using it for linked server purposes, which is IMHO not the most optimal way to use sqlserver at all )

    How about checking the connection host name, IPaddress and/or connection protocol using a login trigger to restrict access.

    Also keep in mind, connections may be desired to just check your instances for performance tuning reasons and (non-)scheduled maintenance using SSMS and/or sqlagent as well as to setup and maintain all your security needs.

    Close it down, but keep in mind to avoid the need to stop it to be able to open it up again !

    (e.g. check the logins for sysadmin membership it you want sysadmins to be able to connect and do their stuff)

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • I have to ask ‘Why you are against Linked Server?’ What’s the harm you see in having it attached in SSMS. If you manage User Access properly it should be fine as Linked Server.

  • some interesting linked server refs:

    http://www.sql-server-performance.com/2007/linked-server/

    http://www.sqlusa.com/articles2005/linkedserver/

    http://searchsqlserver.techtarget.com/tip/Tricks-to-increase-SQL-Server-query-performance

    http://www.sqlservercentral.com/articles/Linked+Server/62246/

    http://www.benjaminnevarez.com/2011/05/optimizer-statistics-on-linked-servers/

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Thank you for your reply.

    I am not against the linked server usage. Actually i need to use it for my report creation from databases at the 3 different servers.

    But I got this weird requirement: only 3 SQL Servers installed in 3 different server machines can access the databases, no other machines in the network can access the databases. I think this can be done using some network securities. But I need to implement this requirement from the software side by some configurations available in SQL server. I am using both SQL Server 2005 and SQL Server 2008 versions.

    I used the LOGON trigger option and it is working fine. Here I am getting the IP for the connection and it is authenticated using the entries configured in a table. Is it a good practice ? If the IP got changed the authentication will fail 🙁 Do you any other alternate idea?

  • LOGON triggers will get triggered at each login attempt. It will not be good for on performance grounds.

    Setting up securities on network (firewall) is a good idea. Why don’t you try to convince Management on it? I believe it’s a good practice to secure your database (server) by reducing surface area than checking the connection at runtime.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply