Restricting access to EM

[ATV4FUN]

First some explaination then the question. I have some users that legally or otherwise have gotten copies of SQL 2000 and installed it on thier local PC's. They are now using Enterprise Manager to connect to my database servers via IP and server name. They are using thier regular user id and passwords that they would use to log into the HR and Finance applications. For obvious reasons this is not a good thing. Now the question, can I some how restrict connections via EM to just thoses with an sa role? Or am I doing something else wrong or missed some hidden configuration.

Thanks in advance!

T.C.

[MAK]

Here are some recommendations and suggestions

for production servers,

1. you can change the port numbers and app should connect using those ports.
This is both secure from external hackers and internal smartasses

2. you can create a proc (and run as a job) that checks the sysprocesses table (column "program name" for value "MS SQL EM") and kill the process based on
based on host name or login IDs.

3. Do not give out production userid and processes. All our apps connect to sql server via registry settings. encrypted (RC4). if people try to decrypt and connect to HR and database
containing sensitive data, it is a security breach and they would be fired immedietly.

4. Run Security audit on all production boxes (u can use some apps like Vigilent and NetIQ)

[MAK]

5. You can have the box behind the firewall.
6. change the architecture (this will take time) to use single login to connect to database through app. and do encryption.

[ATV4FUN]

Thanks Mak, I'll give the proc a shot. Just for the record we don't give out the user id & password the application uses to connect to the db. The users are using thier sql auth logins to the application to register the server(s) in EM.

[MAK]

Then deny access of the users to production box. They have to connect only through APP.

[ATV4FUN]

I'm not sure I understand your last post. But here is another example.

We have appserver1 which servers the HR application to the user, using sql authentication and which also provides the connect to the HR database on dbserver2. The users are using their user id and password for the HR application to bypass appserver1 and connecting(registering) dbserver2 via EM. They can do this because the HR application uses sql authentication. SQL server sees them as valid users.

Does this example help any?

[rmiao]

Then the only sulotion is remove sql tools from user's pc, but still can't stop them connect from other pc that has sql tools installed. Use application role in apps if possible.

[ATV4FUN]

MAK, I tried the proc idea, it didn't work. It will kill the processes, but they are quickly recreated by SQL Server. Same is true if killing through EM. Thanks anyway!

Originally posted by MAK
Here are some recommendations and suggestions

for production servers,

1. you can change the port numbers and app should connect using those ports.
This is both secure from external hackers and internal smartasses

2. you can create a proc (and run as a job) that checks the sysprocesses table (column "program name" for value "MS SQL EM") and kill the process based on
based on host name or login IDs.

3. Do not give out production userid and processes. All our apps connect to sql server via registry settings. encrypted (RC4). if people try to decrypt and connect to HR and database
containing sensitive data, it is a security breach and they would be fired immedietly.

4. Run Security audit on all production boxes (u can use some apps like Vigilent and NetIQ)

[MAK]

You cant do anything now.. You have to change the architecture...