3rd party Application security issues

Question

3rd party Application security issues

AVB

SSChampion

Points: 11418
More actions
March 25, 2011 at 2:43 pm

#248642

We have a 3rd party application that we just installed and it uses a SQL DB. I just noticed under SQL Logins that there is a user login for each user created within this application. Each user has SecurityAdmin and SysAdmin server roles assigned to them. Although they have no databases mapped under "User Mapping" I'm concerned that any user created in this app has those roles.
I've tried logging into EM using one of the users and it fails however I'm concerned this is a security issue. Is this really a valid concern? TIA.

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

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

runaldo SSCommitted Points: 1682 More actions · Answer 1

This is quite common. Sometimes vendors installs and "configures" applications that has excessive rights due to limited knowledge by the people who does the setup. The application probably needs on account with securityadmin rights to create users and assign rights in the db. I normally ask for the admin guide or installation guide by the vendor or the application owner and see how things are meant to be.

There is a lot of strange stuff out there so if it's the case that these privledges needs to be granted to have the application running I would have isolated it. Either by own instance and remove privleges like xp_cmdshell and so on. If there is integration with filesystem/os using these functions I would use dedicated server for this purpose.

AVB SSChampion Points: 11418 More actions · Answer 2

runaldo,

Thanks for the reply.

I'm a little confused as to why each user is created with those rights yet they aren't mapped to any database. I also can't login to EM with any of the accounts. All logins are enabled and appear to be valid.

Any suggestions for locking these users down?

runaldo SSCommitted Points: 1682 More actions · Answer 3

Ok so we have a situation with application is creating sql logins with sysadm rights and then you can't login with them. Do you have the pwd?

I would have created a script which checks for which users have sysadmin rights. Say you have 2-5 logins that should have sysadmin on the instance and all others are considered "unknown". For all the "unknown" - change the default db to the application and grant dbo rights in the database. This way the login comes straight into its application db and can do whatever there but not on the instance. There might be some appliction roles that requires msdb or job ownership but let the application fail if this happens so you can sort out what privledges are needed.

Agree?

AVB SSChampion Points: 11418 More actions · Answer 4

runaldo,

Thanks for the suggestion.

Yes I have the passwords but the application encrypts the passwords so I actually have to go into SQL to change that password if I want to log into EM.

If I create a user in the application and use the password 'Test' it will create an SQL login but 'Test' will have be "encrypted" and that encrypted password is actually used for the SQL login so I can't actually log into EM using 'Test'.

So I go into EM and change the SQL password and voila I can login to EM (but not the app).

Now here is an interesting problem. If I remove the ServerAdmin role from the SQL user I can't log into EM with that user (login failed error 18456). As soon as I add that role back to that user I can log back into EM. I don't understand at all why SQL is requiring that user to be a ServerAdmin.

When I run a security trace in profiler I get this:

Login failed for user 'testuser'. Reason: Login-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: <local machine>]

AVB SSChampion Points: 11418 More actions · Answer 5

I found the problem to the login issue. That user was denied permission to connect to the DB. However if they are part of the ServerAdmin role it overrides that permission setting. Interesting.

runaldo SSCommitted Points: 1682 More actions · Answer 6

You need to explicit grant the user access to the db and set default db to the database in which the user is to access.

Glad it worked out for you! This is much more comfortable than having all users as sysadmin.

AVB SSChampion Points: 11418 More actions · Answer 7

Yes it is.

This app has users access data or update data on the master and msdb table. What I want to do is create a Database role under the master DB and assign applicable users to it. This way I can lock these users down.

First thing is the Syslogins table needs to be accessed. The app verifies the user exists before allowing changes to be made.

select * from master.dbo.syslogins where loginname = 'TestUser'

Edited:

The user can read data from that table but it only sees info for the SA user and itself. So the query above doesn't return anything.