May 8, 2014 at 9:11 am
Our company has a bunch of legacy Access databases. 99% of what they do is connect to SQL Server, but the end-users are extremely married to the Access interface and completely unwilling to switch to ReportServices.
My problem is the security. The connections are all being made with connection strings that have username/password combos. The Access developer has figured out that she can go to SSMS and log directly in as those username/password combos and then have those permissions in the production server and she is abusing it. We've talked to her about it and she continues to do it anyway. The company won't reprimand her because they won't fire her (LONG story).
What can I do? I haven't pissed around with Access in over a decade. Is there any way to provide the connection they need without the username/password being known?
May 8, 2014 at 10:44 am
I'm seeing info about using an ODBC connection instead. That way the credentials would be masked. Isn't that a big performance hit though?
May 8, 2014 at 1:03 pm
are the users in access using hardcoded connection strings (in Access) to connect to SQL...if so can you please provide example?
what authentication method are you using for SQL?
does the Access app have a "log in" at the start?
are the computers that are connecting to SQL set up with local DSNs?
just a few q's to start off with.......
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 8, 2014 at 1:32 pm
Your prospects are utterly bleak, I'd say.
If I understand this correctly, you only want the Access developer, to access SQL Server through Access and through SSMS.
You can set up a DDL trigger which throws here out if app_name() does not return whatever is returned for Access. But she can easily fake that in the login form to SSMS. Of course, that could be construed as a workplace violation, but it seems that you are a loser in the political game.
Then there is more heavy-duty solution: put Access on Terminal Server, segment the network so that she cannot access SQL Server from her desktop. This is a common advice for two-tier applications. But in this case, a developer and a lot of people who are lovers of Access? You know that better than me, but from what you said, I don't expect any success.
I think the best you can do is to review what permissions she needs. I know nada of Access, but reasonably she would need db_datareader/writer and maybe EXEC on the dbo schema. If she need anything beyond that, she will need to ask you to perform those actions.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
May 8, 2014 at 1:49 pm
She needed to be able to add a button to her "application" (aka Access form) that when pushed, will fire off a SQL stored proc that calls a SQL Agent job in which the step is an SSIS.
So I had to give her a user that has db_owner on the db where the proc is and the following on msdb... yes, I said msdb...
db_owner
db_ssisoperator
db_SQLAgentOperatorRole
It's killin me! I want those Access db's gone so bad.
May 8, 2014 at 1:56 pm
Good news! Read this article on my web site. It includes two examples how you can package the permissions to start a job in a stored procedure. Implement that, and you can drop a lot of her permissions.
http://www.sommarskog.se/grantperm.html
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
May 8, 2014 at 1:59 pm
any reason why Access shpuld have to start Agent Job?....cant the job be scheduled to run every n mins?
how long does the job take anyway?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 9, 2014 at 1:30 pm
The job is only meant to be run manually. There are steps that have to be done on someone else's end creating and putting files in place, etc. It's archaic and dumb, but it's what they know and it's how they think they want it.
May 9, 2014 at 1:39 pm
robin.pryor (5/9/2014)
The job is only meant to be run manually. There are steps that have to be done on someone else's end creating and putting files in place, etc. It's archaic and dumb, but it's what they know and it's how they think they want it.
fair enough Robin...doesn't sound like you are going to win this easily.
you mentioned "abusing permissions" .....is this from your perspective or a real corporate issue that could potentially damage the company/data integrity....if the later then I fear you will have to go to war :blink:
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 10, 2014 at 11:39 am
I second Erland's approach: create a sproc that will perform the tasks that need to be done and use one of the options described in the article he referenced.
We do the very same to let a user with low privileges start a SQL Agent Job.
As a side note: It doesn't matter if she has the permission to do what that SQL Agent Job does since this is done using the privileges being used to call the Job (either the Windows account that's running sql agent or the proxy being used).
May 13, 2014 at 9:44 am
Thank you everyone for the advice and info. I've had to concede that this is just how it's going to be and this person is going to do whatever they want and nobody is going to do anything about it. I'll just have to wait for fallout and make sure everyone knows what caused it.
May 13, 2014 at 3:27 pm
Did you look at my article? Technically, there is a way that you can avoid that this person has all those permissions.
Then I sense that there is a political game in which you appear to not have many powers. The only advice I can give there is that you make a clear statement that you cannot make any guarantees about the security on the system the way it is now. Else you may be the person hung when bad things happen.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply