different permissions for user depending on the networks

Question

Post reply

different permissions for user depending on the networks

Geißler Reinhard

SSC Rookie

Points: 29
More actions
June 17, 2009 at 5:22 am

#211072

Hi all,

we currently use a port mapping on the firewall to prevent logins to MSSQL Server 2005 from the office to the production networks.

Our network team want to upgrade the firewall. With the new one we have no possibility.

Now I am looking for a solution to limit logins on MSSQL server 2005 depending on different networks.

Return to the starting point.

Our software uses "linked databases". That means network connections are stored in a table of the database to get to the other.

For our application it is a transparent solution in which database the data are stored.

Our development restored productive databases on their test equipment for performance tests.

To prevent the "linked database" use the production databases we block the default port for MSSQL (1433).

We use an alternate port which is mapped on the firewall and set to port 1433.

example:

production network 10.37.x.y

office network 10.42.z.w

Can I limit different networks? For example with different ports

Or can I limit it depending on the user? for example office_user and productive_user

Which possibilities do I have?

Thanks in advance

Reinhard

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

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

arussell_10 SSCrazy Points: 2110 More actions · Answer 1

I'm not quite sure I understand your question, so this might not be the best answer. But, have you tried to use end points or set the port number to something besides 1433. I believe with end points you can specify exactly what or who has access. For example, you can set a TCP endpoint with a TSQL payload, then start the endpoint and grant connect access to it. Endpoints also let you shut down access by disabling them. Anyway, just my thoughts. I have not used endpoints myself, but I think they might work for you.

Dennis gibson-431475 Old Hand Points: 344 More actions · Answer 2

Dennis gibson-431475

Old Hand

Points: 344

June 18, 2009 at 9:02 am

#1011834

What about seperating your networks using VLAN's?

Joe Joyner SSC Enthusiast Points: 140 More actions · Answer 3

SQL 2008 has new trigger/audit abilities that would allow you to validate the IP address where the user connected from and cancel the connection or log them off(http://technet.microsoft.com/en-us/magazine/2008.04.sqlsecurity.aspx). Since you are using database views to handle access to production or the development you can set the user access on the views and remove all access from the production database. You will need to turn cross-db ownership chaining on.

Geißler Reinhard SSC Rookie Points: 29 More actions · Answer 4

excuse me, for coming back so late..

the feature of MSSQL 2008 would be the feature I need, but we couldn't change the our environment so fast.

We have a couple of dozen different applications running.

For some application we need the release of our costumer (big concern).

the idea with the endpoints I validated before.

With this options we couldn't solve one open case.

again an example which should illustrate the problem by using an endpoint better.

1. ProductivDB1 has an link to ProductivDB2 with an special user called: link_ProductivDB1_to_ProductivDB2

2. ProductivDB1 is transfered to the development environment.

3. TestDB1 has an link to ProductivDB2 with an user changed to: link_TestDB1 _to_ProductivDB2 and the port from 1433 to 14330

4. Parts of the TestDB1 got back by a script to the productiv environment.

5. If the productiv MSSQL Server has the default port 1433 and an endpoint is configured with 14330, nobody would notice that the database is running with the wrong port.

6. Again the database come back to develepment as a new backup with latest data to replay a failure on the test equipment..

The database points to ProductivDB2. Somebody didn't remember to change the dblink in the database. They want reproduce an failure,

manipulate data and affect the ProductivDB2 because they don't get an error to connect to the database.

Joe Joyner SSC Enthusiast Points: 140 More actions · Answer 5

There is an article here on SQLServerCentral.com that discusses logon triggers and how they can be used to restrict access by IP address. This is valid on SQL 2005 SP2 and could be adapted to work in your scenario. It uses the event data to extract the current connection IP address and then can act upon that information.

Please review the article at http://qa.sqlservercentral.com/articles/Security/66151/

Thanks,

Joe