Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Connecting to SQL from MS Access Expand / Collapse
Author
Message
Posted Thursday, May 8, 2014 9:11 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, August 8, 2014 9:24 AM
Points: 51, Visits: 140
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?
Post #1568964
Posted Thursday, May 8, 2014 10:44 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, August 8, 2014 9:24 AM
Points: 51, Visits: 140
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?
Post #1569020
Posted Thursday, May 8, 2014 1:03 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 5:02 PM
Points: 1,899, Visits: 18,917
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 !
__________________________________________________________________
Post #1569075
Posted Thursday, May 8, 2014 1:32 PM This worked for the OP Answer marked as solution


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 3:04 PM
Points: 801, Visits: 715
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
Post #1569081
Posted Thursday, May 8, 2014 1:49 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, August 8, 2014 9:24 AM
Points: 51, Visits: 140
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.
Post #1569083
Posted Thursday, May 8, 2014 1:56 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 3:04 PM
Points: 801, Visits: 715
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
Post #1569085
Posted Thursday, May 8, 2014 1:59 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 5:02 PM
Points: 1,899, Visits: 18,917


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 !
__________________________________________________________________
Post #1569086
Posted Friday, May 9, 2014 1:30 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, August 8, 2014 9:24 AM
Points: 51, Visits: 140
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.
Post #1569434
Posted Friday, May 9, 2014 1:39 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 5:02 PM
Points: 1,899, Visits: 18,917
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


__________________________________________________________________
you can lead a user to data....but you cannot make them think !
__________________________________________________________________
Post #1569437
Posted Saturday, May 10, 2014 11:39 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:04 PM
Points: 7,125, Visits: 13,175
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
Post #1569539
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse