created a linked server, connects fine with sa and can access tables, but cannot with any other sql login.
Is the sql login created on linked sever and does it have permission to the databases you need to access via linked server
1 Like
it is an access database with no password, and the login was created in sql, because they access another linked server on the system
please lay out clearly what your setup is
access database then it links to sql server? I am not getting it.
I had created a linked server in sql which is an access database, created it so they can pull data out of the linked server and put it in the sql database they are using. I can access the data in the linked server when logged in as sa, but not under any other login on the sql server unless they are setup as sysadmin.
How are the users pulling the data via linked server to access db? Do they have a specific tool?
Also where is the access db reside ? On the SQL Server itself or in a networking location?
I moved the access database to the sql server, they are using a query to pull the data from the linked server, but they cannot even access it, when i test the connection when logged in as them, i get authentication failed
I am sure it is a permissions issue, but cannot find where it is
so you moved it to a folder on SQL Server. Does the account running the SQL Service and the users have at least minimum read permission to that folder?
So the users use a query tool like SSMS?
Can you show how you created the linked server - specifically the security page? This should identify how SQL Server will authenticate to access the linked server.
The default setting should be 'Be made using the login's current security context'. If that is checked - then SQL Server will try to impersonate the logged in user to access the linked server and the logged in user will not have access (since that file is on the server - and the users do not have a login to that server).
You can either map each individual user and set them up to impersonate a local login - or try using the option 'Be made without using a security context'. If those don't work then you need to provide the credentials that will be used under the option 'Be made using this security context'.
1 Like
To be honest, even if that database was of such a low value as a simple list of the primary colors, not having proper security on it is a violation to me. Just sayin'...
the bottom error is interesting. do you have an mdw file?
Microsoft Access Workgroup Information in there somewhere?
why do you want to create a linked server to access. please describe your environment clearly. Do others use Access to enter data for an application used in the company but then other users use SSMS to query the access database? is this a one time conversion from access ? or is this an ongoing process?
It could also be that someone else has opened the access file and has it locked
1 Like
no mdw file that i know of, basically, they want to pull data from the RBDMSD97 linked server through a sql query and put it into one of the sql databases on the server. This is a file from the state of ohio dept of natural resources that we download as a .mdb. Had to convert it to .accdb and move it to the server for the linked server to work. No one else has the file open, i already checked
so is it downloaded once a week/month/quarterly? I Would highly recommend using SSIS or powershell to take content of that access data into SQL server, in an automated fashion. it does not involve anyone touch any query (dangerous especially if they can delete data, accountability, sensitive data, auditing) do you have integration services installed or know how to use powershell?
This linked server to access is just not sustainable approach.
1 Like
that makes sense to me, powershell may be the way to do that, let me explore those options. Thank you very much
If the access file is locked, then no other method will be able to get to it either. If the ACE drivers have been working for you in the past, I wouldn't waste my time changing.
the ACE driver works, but cannot access the linked database with anything except sa.