Preventing access to SQL Server from other Servers

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?In your Windows Server environment, Create a Windows Group.
Put your users who will use your application which uses SQL Server
databases, into that Windows Group.
Create a Login for that specific group in SQL Server. Assign necessary
permissions to that Login.
So, only that specific User Group will be able to reach your databases, not
all users in your domain.
--
Ekrem Ã?nsoy
"ben_634" <ben634@.discussions.microsoft.com> wrote in message
news:1B8EE3F5-15C3-4009-AD42-25DFA6E37A60@.microsoft.com...
> 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?|||Hi
It sounds like you are connecting with a higher priveleged users such as an
system administrator. In which case they will have access to the tables and
data. You should make sure that you restrict access to high privileged
accounts and that other users have the minimum permissions needed to do what
is needed to do before you set the application role.
John
"ben_634" wrote:
> 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?