May 11, 2011 at 5:27 am
I’m an application programmer and I proved that I could update our production databases via Excel. By connecting using the “Get External Data From Other Sources” option I can generate SQL through the normal process (setting the ODBC connection, picking tables, columns, and setting the relationships) and then clicking the edit SQL option I pull up the SQL that was generated. Using that I can edit it to run a stored procedure that is used in a production application that has public execute. The application has security built in to prevent unauthorized access but since the SP has public execute I can modify production. For example the SP to delete a claim is “cst_DeleteClain 12345678”. I can plug that into Excel and delete a claim where I cannot through the application. My question is can permissions be set up to block a specific program from having access? I’m sure there are best practices to prevent access like I mentioned but I’m wondering about just stopping a particular program from doing it.
May 11, 2011 at 5:47 am
Permissions to public are a security worst practice.
It's not just excel. If the permissions are that bad you could connect from Access, Word (most likely), any querying tool and have the same access. Someone could install SSMS on their desktop and you can't block SSMS as the DBAs will need to use that. Blocking one application is like building a sand castle to hold back the tide.
The best approach would be to fix the security settings, remove that public grant, probably several more things, but hard to advise without seeing the system.
Don't suppose there's scope for getting a security consultant in? Probably a huge project to get it right.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 11, 2011 at 6:12 am
That being understood my question remains unanswered...can a specific program\executable be blocked?
May 11, 2011 at 6:15 am
Sure. Have a read up on login triggers (assuming you're at least on SP2) and the Application_Name() function. Be careful, a mistake in a login trigger can lock everyone out of the server.
That said, it's not going to make the server any more secure. SQL gets the value in Application_Name() from what the originating application passes. It's trivial to change if writing code. The best thing you can do is whitelist not blacklist. List the apps that are allowed to connect, make sure your custom app passes a well-defined application name, make sure you allow whatever the admins use.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 11, 2011 at 6:28 am
Thank you...I passed that on to my project leader 🙂
May 11, 2011 at 7:53 am
Keep in mind that a logon trigger that blocks a certain application (like Excel) will do that for all connections of that application. So if you have other databases that users legitimately need that sort of access with, you're out of luck. Also, this puts you in a difficult situation. You're effectively trying to blacklist applications to prevent access. This never works. I'll just find an app you're not blocking. And if you're smart enough to figure out all the apps I will use, I'll create a File DSN and manually edit it to change the application name to some random gobbledegook and bypass the logon trigger.
A few questions:
- Where does the application run? Is it running on the user workstations?
- Under what security context does the back-end database get run?
- Are there other databases that you're users need to connect to or can you lock this SQL Server down to just this app?
K. Brian Kelley
@kbriankelley
May 11, 2011 at 8:19 am
K. Brian Kelley (5/11/2011)
Keep in mind that a logon trigger that blocks a certain application (like Excel) will do that for all connections of that application. So if you have other databases that users legitimately need that sort of access with, you're out of luck. Also, this puts you in a difficult situation. You're effectively trying to blacklist applications to prevent access. This never works. I'll just find an app you're not blocking. And if you're smart enough to figure out all the apps I will use, I'll create a File DSN and manually edit it to change the application name to some random gobbledegook and bypass the logon trigger.A few questions:
- Where does the application run? Is it running on the user workstations?
- Under what security context does the back-end database get run?
- Are there other databases that you're users need to connect to or can you lock this SQL Server down to just this app?
- Where does the application run? Is it running on the user workstations?
Workstations and Citrix.
- Under what security context does the back-end database get run?
I don't know the answer to that. Maybe if you worded it differently.
- Are there other databases that you're users need to connect to or can you lock this SQL Server down to just this app?
There are other DBs and many apps.
May 11, 2011 at 8:27 am
tyson.price (5/11/2011)
- Under what security context does the back-end database get run?I don't know the answer to that. Maybe if you worded it differently.
What account does SQL Server run as?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 11, 2011 at 9:41 am
GilaMonster (5/11/2011)
tyson.price (5/11/2011)
- Under what security context does the back-end database get run?I don't know the answer to that. Maybe if you worded it differently.
What account does SQL Server run as?
Had to ask a DBA...it's SQL Admin
May 11, 2011 at 9:50 am
Sorry, wasn't clear. The name of the account means little without access to your network. Is it a local machine account? A domain account? What permissions does it have?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 11, 2011 at 9:56 am
GilaMonster (5/11/2011)
Sorry, wasn't clear. The name of the account means little without access to your network. Is it a local machine account? A domain account? What permissions does it have?
Is that information I can get via Mangaement Studio or a query? I don't want to step on our DBA groups toes by sniffing around in their territory. So far I'm just passing the information from here to my project leader.
May 11, 2011 at 10:00 am
No. It's info got from configuration manager and Active Directory.
Brian's asking to try and ascertain just how much risk there is, whether this security setup just risks the database server (probably the entire database server) or whether it risks the domain as well.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 11, 2011 at 10:00 am
tyson.price (5/11/2011)
- Under what security context does the back-end database get run?I don't know the answer to that. Maybe if you worded it differently.
Actually, I typed this wrong because my brain was ahead of my fingers.
How does the application connect to the back-end database? In the context of the user or using a service account (SQL Server login)?
K. Brian Kelley
@kbriankelley
May 11, 2011 at 10:41 am
K. Brian Kelley (5/11/2011)
tyson.price (5/11/2011)
- Under what security context does the back-end database get run?I don't know the answer to that. Maybe if you worded it differently.
Actually, I typed this wrong because my brain was ahead of my fingers.
How does the application connect to the back-end database? In the context of the user or using a service account (SQL Server login)?
Visual Basic ADODB using the user's credetials. This is a typical connection string:
Server=SERVERNAME;Database=DATABASENAME;Driver=SQL Server;Trusted_Connection=Yes
May 11, 2011 at 11:47 am
Ugh. In that case I would move it off to its own instance and lock that instance down using Logon Triggers. There's not a whole lot of other options available to you.
If users need to query certain tables, then use SSIS or replication to copy over the tables they are interested in to another instance of SQL Server and grant them the appropriate access on that instance and database.
K. Brian Kelley
@kbriankelley
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply