Linked Server is not configured for data access Error: 7411 in SQL Server 2005

Tuesday, March 24, 2009 |

I wrote one article to set Linked server at:

http://www.sqlhub.com/2009/03/linked-server-in-sql-server-2005-from.html

Sometime you properly set the link server and while trying to execute SELECT statement over it, it may show you following error.

Msg 7411, Level 16, State 1, Line 2

Server 'LinkToAceess' is not configured for DATA ACCESS.

Solution of this error is really pretty much simple. Before we move to solution of error I would like to raise this error. Run the linked server example given in above article and see whether it is working properly. One you done with that let us change one property of Linked Server which will raise above said error.

--set data access property to 'false' which will generate error

--when you try to execute any SELECT statement against your linked server

exec sp_serveroption [LinkToAceess],'Data Access','false'

Now try to execute SELECT statement and greet the error:

Msg 7411, Level 16, State 1, Line 2

Server 'LinkToAceess' is not configured for DATA ACCESS.


Solution is so simple; just enable your data access to linked server with following command.

exec sp_serveroption [LinkToAceess],'Data Access','true'


Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

4 comments:

sundip shah said...

msuHi, I have a problem reading from the sql database on a site i got access to.
this is my code:
con.open "PROVIDER=SQLOLEDB;DATA SOURCE=mysql2.siteam.co.il;UID=hoops_admin1;PWD=db123;DATABASE=hoops_db1"
the site which reads from this database is fine but when i'm trying to read from it in my localhost i get this error:
Error Type:
Microsoft OLE DB Provider for SQL Server (0x80004005)
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
/hoops.co.il/includs/DBcon.asp, line 6

riteshji help me please?

Ritesh Shah said...

Hi Sundip,

As per my investigation, I think there is not error in your connection string. The only guess I have is, you have provided wrong credentials or spell mistake in db name.

Thanks,

Ritesh Shah

Ritesh Shah said...

However you can try following string in your connection string. Don't forget to change database server name and its credential.

"data source=your db name or IP;initial catalog=your db name;password=your db password;persist security info=True;user id=your db user;packet size=4096;Connection Lifetime=202130;Pooling=false"

Anonymous said...

Thanks! This saved my ass. :)