Set up a linked Access Server without any security

[seaport]

It seems to be a very simple job and I did it a couple times before. But I simply could not get it work this time.

The Access database file is on the same workstation as the SQL server. It has not workgroup security on it. I even gave all users full control on this file.

I set up the linked server. The connection was tested OK. In the Linked Server Properties - Security screen, Under the question "For a login not defined int he list above, connections will", I chose "Be made using the login's current security context," and it worked.

What I really want is the option "Be made without using a security context." If I choose that, I got an error "There is no remote user '' mapped to local user '(null)' from the remote server. (Microsoft SQL Server. Error: 15466)"

Any suggestions?

Thanks in advance.

 

[mrdenny]

To access a file on the file system you have to access it as a user. Access via NULL user isn't allowed.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2008 Implementation and Maintenance / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Admin (SQL 2005/2008) / Database Dev (SQL 2005)

My Blog

 

[seaport]

Denny,

On another workstation's SQL Server 2005, I added a not-secured Access linked server without any problem. All I did was to specify the file location. The setting for "For a login not defined int he list above, connections will" was default to "Be made without using a security context". There was no error message at all.

FYI, the master database on the SQL Server with the problem was a copy from another SQL server. You helped me on that issue in another thread

http://www.tek-tips.com/viewthread.cfm?qid=1536624&page=1

I am not sure whether this is relevant.

Seaport

 

[danvlas]

Be made using this security context:

User = Admin
Password = Leave Blank


HTH


Daniel Vlas
Systems Consultant

 

[seaport]

No. This did not work.

I got the error -
There is no remote user 'Admin' mapped to local user '(null)' from the remote server.

I thought there is something not right about the Access database. So I remade the database, but I still got the same result.

 

[seaport]

To be more specific, when I set up an Access linked server, the security option was made to "Be made without using a security context."

I got the following error after clicking "OK" to finish the setup.
There is no remote user '' mapped to local user '(null)' from the remote server. (Microsoft SQL Server. Error: 15466)

The linked server was actually created, but the security option was switched to "Be made using the login's current security context."

Under this option, I was able to access the linked server using Management Studio without any problem. However, my ASP.net application accesses the SQL server using a SQL login, not a windows login. Now this asp.net application was denied access to the linked server. The error is:

The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "Test_DB" reported an error. Authentication failed.
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "Test_DB".

The web application was working well with another SQL server, where the linked server was setup with the option "Be made without using a security context."

Seaport

 

[seaport]

Denny,

It turned out that the problem is caused by the master database I got from another SQL Server. I just rebuilt the master database, using the setup disc, to have a fresh start. After that, I set up the linked server exactly the way I wanted.

Seaport