October 9, 2007 at 7:51 pm
ben_634 1hr 20mins ago
I posted this question before but I'm not sure it was fully understood:
I'm using MSSQL7, NT authentication and application roles so only my application can access the data. Also, other applications (like Excel) can not access the data and read it. So far, so good...
Yet, I noticed that if I try to access the SQL Server from another SQL Server on the network, it is allowed to see the list of tables, SP, etc. It is not allowed to open the table, but the Import/Export wizard is working and will allow retrieving data from the secured tables.
If I change to MSSQL authentication, any user will be able to access the data from my application and I don't want that either.
Unless I'm missing something, this is a big problem, especially today where any VPN connection with valid user name and password can actually log in to the domain and therefore connect to the database via SQL Server.
By the way, the server still must allow access to users via applications so logins must exist. I just don't want other SQL servers on the network to be able to connect to and import/export, view table and SP, etc.
Any ideas?
October 10, 2007 at 8:31 am
There sounds like there is a security issue with the SQL Server you are trying to protect.
In order to connect, the client must have a valid login. How is the other SQL Server connecting when you are using SQL Server-based authentication? Are you specifying the username/password?
You need to look at what permissions each login has (and what logins are present). You also need to look at how the logins map into each of the databases and what permissions are granted to each login. Look out specifically for whenever guest is enabled at the login level and in any user databases.
K. Brian Kelley
@kbriankelley
October 10, 2007 at 7:14 pm
Thank for you reply...
I think I got the roles and logins all set up popery.
What I don't understand is why the remote SQL Server is not able to Open a table (View All Rows) or design a table but is still able to retrieve the data by using the Import/Export tools.
That's the mystery to me...
October 10, 2007 at 8:00 pm
Are you using a Windows login to connect to the first SQL Server? How do you have the linked/remote server security set up? Also, when you say data extraction tools, chances are they make one connection to the first SQL Server and make a second connection to the other SQL Server. Where they run is wherever the client is (it may even be your workstation). This isn't the same as connecting through a linked server connection.
K. Brian Kelley
@kbriankelley
October 11, 2007 at 6:08 pm
I apologize if I bug you too much... you can freely tell me to go find solutions another place, so feel free to do so...
Just in case you still want to help:
1. I'm running the DB on MSSQL7 on Win2000 machine.
2. All users login to MSSQL using WinNT authentication (for login only). Access to database is not allowed except of using specified Application Roles.
3. I have MSSQL7 on my local machine (I registered the main server on my machine). I'm a member of the WinNT group mentioned above only.
4. I can not retrieve data from the DB using Excel, etc. Even the MSSQL on my local machine can not design or perform SELECT from the tables.
5. BUT, I can use the MSSQL on my machine to see the names of table, SP and views. I can also use the Export/Import tools to extract data from the main MSSQL.
What am I missing?
Thanks...
October 17, 2007 at 1:25 pm
ben_634 (10/11/2007)
5. BUT, I can use the MSSQL on my machine to see the names of table, SP and views. I can also use the Export/Import tools to extract data from the main MSSQL.
Do you mean you can use the tools? I forget what the tool is called in SQL Server 7.0, but it's like SQL Server 2000's Enterprise Manager (may even be called Enterprise Manager). If you can connect with it, it's not SQL Server to SQL Server you are connecting with, rather, it's a client. If so, how are you set to connect?
K. Brian Kelley
@kbriankelley
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply