SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Connecting to SQL from MS Access


Connecting to SQL from MS Access

Author
Message
Robin
Robin
SSC-Addicted
SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)

Group: General Forum Members
Points: 464 Visits: 313
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?
Robin
Robin
SSC-Addicted
SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)

Group: General Forum Members
Points: 464 Visits: 313
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?
J Livingston SQL
J Livingston SQL
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11647 Visits: 37442
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

Erland Sommarskog
Erland Sommarskog
SSCarpal Tunnel
SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)

Group: General Forum Members
Points: 4958 Visits: 875
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.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Robin
Robin
SSC-Addicted
SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)

Group: General Forum Members
Points: 464 Visits: 313
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.
Erland Sommarskog
Erland Sommarskog
SSCarpal Tunnel
SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)

Group: General Forum Members
Points: 4958 Visits: 875
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

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
J Livingston SQL
J Livingston SQL
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11647 Visits: 37442
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

Robin
Robin
SSC-Addicted
SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)

Group: General Forum Members
Points: 464 Visits: 313
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.
J Livingston SQL
J Livingston SQL
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11647 Visits: 37442
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

LutzM
LutzM
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22525 Visits: 13559
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).



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search