July 18, 2019 at 9:46 am
We have a manufacturer whose software requires authorization for the master database. (only db owner working) Unfortunately, the manufacturer can not tell us why this access to the master database is necessary. Without Permission the application will not start
What are your opinions? Have you ever seen such software? We have 200 different applications but this is the first one that needs permanent access
Is there any objection to security?
As an alternative, we can install an SQL Express instance because the database is very small. If access to the master DB from your point of view is not a good idea
Thanks in Advance Guys BR Julian
sql-server
July 18, 2019 at 11:59 am
i can't think why any application would need access to the master database, i would avoid using SQL Express because of the limitation on resources. I would push back to the supplier, application should be developed so users have the minimum permissions why someone would create an app to check for X in mater Db sounds like a cowboy dev to me. i bet when they set the system up they will give full DBO to everyone lol, run away now while you can.
if not too far i would threaten going to another supplier for that type of product, that said you could always create a standalone instance, install the software and then SQL trace the app starting up to work out what its does with master.
DEFO SECURITY CONCERN
***The first step is always the hardest *******
July 18, 2019 at 12:29 pm
Hello, thank you for your answer.
Unfortunately, I could not figure out why the application does not start without masterdb access because there is no table, function, stored procedure etc in master db from this software.
Unfortunately, the software manufacturer does not know why access to the MasterDB is necessary
July 18, 2019 at 1:17 pm
If it's only access, it is often done through the guest account which is enabled in master and should be kept enabled in master (as well as tempdb and msdb). If guest is still enabled and that app won't start and if just one login for the application needs access, you could temporarily add that login to master and run extended events session or profiler and see what that account is accessing in master or what it needs in master.
Sue
July 18, 2019 at 1:25 pm
this just gets better, they don't know!, what kind of software company doesn't know what the application does and why it does it.
This to me is a real security risk, its flashing with big neon sign.
Do they actually need access to Master or do they need to some one of the server principles. did you set up a SQL profiler trace and start their services? does any code get executed against the master DB for example are they trying to look to see if a specific DB exists if not create it.
Deny access and raise a live call with them to fix it or your not paying them
***The first step is always the hardest *******
July 18, 2019 at 2:52 pm
Yes I've seen applications that for whatever reason require both sys admin access to the instance and specific collations on system DB's. I would just give the application it's own instance with it's own service account so it can't mess with anything else.
July 18, 2019 at 5:12 pm
This is not unusual, unfortunately.
A lot of apps require sysadmin during install, so it can create the database, jobs, etc but usually it can be removed once installation is completed.
Are you saying that, at startup, the app checks if the login is db_owner in master, and if not, fails to start??
That's weird, and I would look for another vendor.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
July 18, 2019 at 10:51 pm
I've seen this, but with trace/XE, you can usually tell what they're doing. In a few cases, they add a login, so they don't need dbo or sysadmin, but the vendor devs just don't know that. I've actually just added my own logins before (or jobs, etc.) and let the application "pick them up", because it doesn't need to do x, it just needs to work with some change that's been made to the system.
My advice is figure out what is needed. If you can get another vendor, I'd do that as well, telling them why. Barring that, give them their own instance and audit things that might cause you problems, like changing logins, running jobs, changing settings, etc.
July 19, 2019 at 2:26 am
What does the software do?
--Jeff Moden
Change is inevitable... Change for the better is not.
July 19, 2019 at 8:21 am
Hi Guys,
thank you all for your reply.
another manufacturer is no alternative according to the department.
However, I also want to prevent the db from doing mysterious things.
As soon as I remove the db owner role, the application can not be started because the SQL server is not found.
Do you have any idea how this could be related to the Master DB?
The best thing to do is try to find out with a trace or use extended events if something is tried in the master DB when the application is started,
July 19, 2019 at 11:24 am
Hi Guys, thank you all for your reply. another manufacturer is no alternative according to the department. However, I also want to prevent the db from doing mysterious things. As soon as I remove the db owner role, the application can not be started because the SQL server is not found. Do you have any idea how this could be related to the Master DB? The best thing to do is try to find out with a trace or use extended events if something is tried in the master DB when the application is started,
Well DB's don't just do mysterious things, they do what applications or users tell them to do. You can try to find out what it's doing that would need DBA access and see if you can give it more restrictive permissions but you might also end up in a place where if you ever need support the vendor will just blame you for not giving the app what it wants.
July 19, 2019 at 12:35 pm
Hi Guys, thank you all for your reply. another manufacturer is no alternative according to the department. However, I also want to prevent the db from doing mysterious things. As soon as I remove the db owner role, the application can not be started because the SQL server is not found. Do you have any idea how this could be related to the Master DB? The best thing to do is try to find out with a trace or use extended events if something is tried in the master DB when the application is started,
Again I ask, what does the software do? It may lead to an explanation.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 19, 2019 at 4:28 pm
My guess, it connects with a default database of master, because the developer is a moron and beyond lazy.
If you can set the connection string, set a default db, or change the one the app uses and see if that fixes things.
July 22, 2019 at 12:41 pm
the software is collecting measuring data for welding .
the data was migrated from the manufacturer from MS-Access to MS-SQL.
July 22, 2019 at 12:54 pm
the software is collecting measuring data for welding . the data was migrated from the manufacturer from MS-Access to MS-SQL.
Hmmm... then the only reason I can think of why they might need such privs to the Master DB is because it uses something from the "sys" schema and they used the 3 part naming convention in their code instead of the 2 part naming convention.
You could try to find out what it is by either using SQL Profiler or Extended events to look for hits on the master database by anything with a spid > 50. You'll have to watch whatever trace you setup to do that because it could end up collecting a shedload of data.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply