Cannot edit MS Access tables that are linked to SQL Server 2000

I am not able to edit tables through MS Access grid that is linked to sql
server 2000. I am able to edit some tables but not all.
Can you please tell me how I could change this so that I am able to edit all
the tables.
Thanks
> I am not able to edit tables through MS Access grid that is linked to sql
> server 2000. I am able to edit some tables but not all.
> Can you please tell me how I could change this so that I am able to edit
all
> the tables.
I guess you don't have Primary Key constraints on tables you cannot modify.
Check this first.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

Cannot edit MS Access tables that are linked to SQL Server 2000

I am not able to edit tables through MS Access grid that is linked to sql
server 2000. I am able to edit some tables but not all.
Can you please tell me how I could change this so that I am able to edit all
the tables.
Thanks> I am not able to edit tables through MS Access grid that is linked to sql
> server 2000. I am able to edit some tables but not all.
> Can you please tell me how I could change this so that I am able to edit
all
> the tables.
I guess you don't have Primary Key constraints on tables you cannot modify.
Check this first.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

Cannot edit MS Access tables that are linked to SQL Server 2000

I am not able to edit tables through MS Access grid that is linked to sql
server 2000. I am able to edit some tables but not all.
Can you please tell me how I could change this so that I am able to edit all
the tables.
Thanks> I am not able to edit tables through MS Access grid that is linked to sql
> server 2000. I am able to edit some tables but not all.
> Can you please tell me how I could change this so that I am able to edit
all
> the tables.
I guess you don't have Primary Key constraints on tables you cannot modify.
Check this first.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

Cannot drop Temporary Table linked to Connection

Hi there!

I have a Java-Servlet that opens a connection to a SQL-Server 2000
With this connection I create a temporary local table.
I can call the temporary table within this connection as often as I want ... it's there as long as the connection is open. It's not dropped automatically as long as the connection is open. But when want to drop that table MANUALLY, I get a

[Microsoft][SQLServer JDBC Driver][SQLServer]Cannot drop the table '#tablename', because it does not exist in the system catalog.

I don't use any stored procedures that could drop the table before I do.
The database is accessed only by "normal" sql-statements through the JDBC-Driver. I can read the content of the table 10 times but if I try to drop it just in the next line, I get the above error message.

Now I know that the table will be dropped as soon as I close the connection, but anyway it's safer to do it manually ... so has somebody an idea?

Thanks

LookDo you need repeated access to the temp table or is it used just once ? Also, can you send the code you are using ? Is it straight sql or are you using a stored procedure (it appears that you are doing straight sql)?|||Found this in MSDN. Look for the article "Coding T-SQL like an Expert"

IF (object_id('tempdb..#mytemp') IS not Null)
Drop Table #mytemp

Although `tempdb..#authors_temp' is a truncation of the actual physical name of the object as held in tempdb..sysobjects, somehow OBJECT_ID() recognizes that you mean "your" temporary table called #mytemp.

Cannot create Linked Server.

Hello.
Can you help me?
I need create Linked server for Visual Fox Pro, but I can not create it.
For DBase no problem, but for VFP, no.
May be anyone have script?
Thank you very much.Try...
exec sp_addlinkedserver
'myVFP',
'',
'MSDASQL',
null,
null,
'Driver=Microsoft Visual Foxpro Driver;
DBQ=;
SourceType=DBF;
SourceDB=x:\VFP'
exec sp_addlinkedsrvlogin 'myVFP',false,null,null,null
-oj
Rac v2.2 & QALite!
http://www.rac4sql.net
"Yuriy B. Belyaev" <jurijb@.mail.ru> wrote in message
news:%23LT5fylhDHA.2536@.TK2MSFTNGP10.phx.gbl...
> Hello.
> Can you help me?
> I need create Linked server for Visual Fox Pro, but I can not create it.
> For DBase no problem, but for VFP, no.
> May be anyone have script?
> Thank you very much.
>
>|||I try
select count(*) from myVFP...goodsfox ("goodsfox" is a table name)
this is message from SQL Server:
Server: Msg 7313, Level 16, State 1, Line 1
Invalid schema or catalog specified for provider 'MSDASQL'.
"oj" <nospam_ojngo@.home.com> wrote in message
news:O9tzICmhDHA.3276@.tk2msftngp13.phx.gbl...
> Try...
> exec sp_addlinkedserver
> 'myVFP',
> '',
> 'MSDASQL',
> null,
> null,
> 'Driver=Microsoft Visual Foxpro Driver;
> DBQ=;
> SourceType=DBF;
> SourceDB=x:\VFP'
> exec sp_addlinkedsrvlogin 'myVFP',false,null,null,null
>
> --
> -oj
> Rac v2.2 & QALite!
> http://www.rac4sql.net
>
> "Yuriy B. Belyaev" <jurijb@.mail.ru> wrote in message
> news:%23LT5fylhDHA.2536@.TK2MSFTNGP10.phx.gbl...
> > Hello.
> >
> > Can you help me?
> > I need create Linked server for Visual Fox Pro, but I can not create it.
> > For DBase no problem, but for VFP, no.
> >
> > May be anyone have script?
> > Thank you very much.
> >
> >
> >
>|||do:
select *
from openquery(myVFP,'select count(*) from goodsfox')
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net
"Yuriy B. Belyaev" <jurijb@.mail.ru> wrote in message
news:e17ZsHmhDHA.3784@.tk2msftngp13.phx.gbl...
> I try
> select count(*) from myVFP...goodsfox ("goodsfox" is a table name)
> this is message from SQL Server:
> Server: Msg 7313, Level 16, State 1, Line 1
> Invalid schema or catalog specified for provider 'MSDASQL'.
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:O9tzICmhDHA.3276@.tk2msftngp13.phx.gbl...
> > Try...
> >
> > exec sp_addlinkedserver
> > 'myVFP',
> > '',
> > 'MSDASQL',
> > null,
> > null,
> > 'Driver=Microsoft Visual Foxpro Driver;
> > DBQ=;
> > SourceType=DBF;
> > SourceDB=x:\VFP'
> >
> > exec sp_addlinkedsrvlogin 'myVFP',false,null,null,null
> >
> >
> > --
> > -oj
> > Rac v2.2 & QALite!
> > http://www.rac4sql.net
> >
> >
> > "Yuriy B. Belyaev" <jurijb@.mail.ru> wrote in message
> > news:%23LT5fylhDHA.2536@.TK2MSFTNGP10.phx.gbl...
> > > Hello.
> > >
> > > Can you help me?
> > > I need create Linked server for Visual Fox Pro, but I can not create
it.
> > > For DBase no problem, but for VFP, no.
> > >
> > > May be anyone have script?
> > > Thank you very much.
> > >
> > >
> > >
> >
> >
>

Cannot create linked server

I cannot create a linked to a MySql database in Enterprise Manager. Here is the relevant info:

Trying to create a linked server on a SQL Server 2000 Standard Edition Service Pack 4 running on a Windows 2000 Standard Edition server.

The MySQL server is running on Red Hat - not sure of the DB or OS version. However the DSN I created connects to the server correctly. I can connect using any of the MySQL tools.

I am using the latest ODBC MySQL drive 3.51...Here is the Provider string I am using...

DRIVER={MySQL ODBC 3.51 Driver} ;SERVER=192.168.1.99;DATABASE=bug_tracker; USER=UserID; PASSWORD=Password; OPTION=3

I have also tried using a System DSN to connect with, but that does not work either. It appears to create the linked server correctly each time, but I cannot access any of the tables. Here is the error message I get each time:

Error 7399: OLE DB Provider 'MSDASQL' reported an error. Datasource name not found and no default driver specified] OLD DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: ]

Sure could use a little help!

Thanks!!!!

Brad Feaker

Please reply to forum only! Thanks!

Try:

EXEC sp_addlinkedserver 'mysql', '', 'MSDASQL', 'your_system_dsn'

EXEC sp_addlinkedsrvlogin 'mysql', 'false', null, 'userid', 'pwd'|||Sorry - have already tried that too. Same error message.

Thanks for your help!

Brad
Ex Nihilo, Nihil Fit|||

There are two possibilities:

1. Incorrect provider-string
2. Incorrect linked server setup

To isolate the issue, try to use the provider string with OPENROWSET syntax. If this works, then try to create a new linked server definition with appropriate login mappings.

|||

Brad,

Sorry, I do not use mySQL so cannot be of much help as far as the correct provider/library.

However, this KB looks like a solution to your problem.

http://support.microsoft.com/default.aspx?scid=kb;en-us;814398

Cannot create linked server

I cannot create a linked to a MySql database in Enterprise Manager. Here is the relevant info:

Trying to create a linked server on a SQL Server 2000 Standard Edition Service Pack 4 running on a Windows 2000 Standard Edition server.

The MySQL server is running on Red Hat - not sure of the DB or OS version. However the DSN I created connects to the server correctly. I can connect using any of the MySQL tools.

I am using the latest ODBC MySQL drive 3.51...Here is the Provider string I am using...

DRIVER={MySQL ODBC 3.51 Driver} ;SERVER=192.168.1.99;DATABASE=bug_tracker; USER=UserID; PASSWORD=Password; OPTION=3

I have also tried using a System DSN to connect with, but that does not work either. It appears to create the linked server correctly each time, but I cannot access any of the tables. Here is the error message I get each time:

Error 7399: OLE DB Provider 'MSDASQL' reported an error. Datasource name not found and no default driver specified] OLD DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: ]

Sure could use a little help!

Thanks!!!!

Brad Feaker

Please reply to forum only! Thanks!

Try:

EXEC sp_addlinkedserver 'mysql', '', 'MSDASQL', 'your_system_dsn'

EXEC sp_addlinkedsrvlogin 'mysql', 'false', null, 'userid', 'pwd'|||Sorry - have already tried that too. Same error message.

Thanks for your help!

Brad
Ex Nihilo, Nihil Fit
|||

There are two possibilities:

1. Incorrect provider-string
2. Incorrect linked server setup

To isolate the issue, try to use the provider string with OPENROWSET syntax. If this works, then try to create a new linked server definition with appropriate login mappings.

|||

Brad,

Sorry, I do not use mySQL so cannot be of much help as far as the correct provider/library.

However, this KB looks like a solution to your problem.

http://support.microsoft.com/default.aspx?scid=kb;en-us;814398

Cannot Create Linked Server

I am trying to create a linked server in Management Studio Exoress. In the Objext Explorer, I open Server Objexts and the right-click on Linked Servers and select New Linked Server. I then get an error that says "Cannot show the requested dialog. Additional information: Cannot find table 0. (System Data). The full text of the error is as follows:

===================================

Cannot show requested dialog.

===================================

Cannot find table 0. (System.Data)

Program Location:

at System.Data.DataTableCollection.get_Item(Int32 index)
at Microsoft.SqlServer.Management.SqlManagerUI.LinkedServerPropertiesGeneral.PopulateProvidersCombo()
at Microsoft.SqlServer.Management.SqlManagerUI.LinkedServerPropertiesGeneral.Microsoft.SqlServer.Management.SqlMgmt.IPanelForm.OnInitialization()
at Microsoft.SqlServer.Management.SqlMgmt.ViewSwitcherControlsManager.SetView(Int32 index, TreeNode node)
at Microsoft.SqlServer.Management.SqlMgmt.ViewSwitcherControlsManager.SelectCurrentNode()
at Microsoft.SqlServer.Management.SqlMgmt.ViewSwitcherControlsManager.InitializeUI(ViewSwitcherTreeView treeView, ISqlControlCollection viewsHolder, Panel rightPane)
at Microsoft.SqlServer.Management.SqlMgmt.LaunchForm.InitializeForm(XmlDocument doc, IServiceProvider provider, ISqlControlCollection control)
at Microsoft.SqlServer.Management.SqlMgmt.LaunchForm..ctor(XmlDocument doc, IServiceProvider provider)
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ToolsMenuItem.OnCreateAndShowForm(IServiceProvider sp, XmlDocument doc)
at Microsoft.SqlServer.Management.SqlMgmt.RunningFormsTable.RunningFormsTableImpl.ThreadStarter.StartThread()

I am running XP SP2 and SSE SP2. One other item is that the providers folder is empty. I checked another box and there are several providers listed in that installation. It looks like when the SSE is installed, the providers are not being created. I have tried uninstalling and reinstalling and am having the same problem. Is this a installation bug or is there a conflict with another program? I also re-downloaded the installation files in case there was a problem with that, but it didn't solve the issue either.

Thanks for any help,

Paul Nelson

I have got the same problem, and I noticed that there is nothing in "Providers" folder under "Linked Servers". Looking at another machine where "Linked Servers" is working properly, the "Providers" folder contains many items.

Cannot create linked server

I posted this in the Data Access forum already - may have been the wrong place...

I cannot create a linked to a MySql database in Enterprise Manager. Here is the relevant info:

Trying to create a linked server on a SQL Server 2000 Standard Edition Service Pack 4 running on a Windows 2000 Standard Edition server.

The MySQL server is running on Red Hat - not sure of the DB or OS version. However the DSN I created connects to the server correctly. I can connect using any of the MySQL tools.

I am using the latest ODBC MySQL drive 3.51...Here is the Provider string I am using...

DRIVER={MySQL ODBC 3.51 Driver} ;SERVER=192.168.1.99;DATABASE=bug_tracker; USER=UserID; PASSWORD=Password; OPTION=3

I have also tried using a System DSN to connect with, but that does not work either. It appears to create the linked server correctly each time, but I cannot access any of the tables. Here is the error message I get each time:

Error 7399: OLE DB Provider 'MSDASQL' reported an error. Datasource name not found and no default driver specified] OLD DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: ]

Sure could use a little help!

Thanks!!!!

Brad Feaker

Please reply to forum only! Thanks!

Seems like no one else knows jack about this either!
|||

try this

Go to security/Linked Servers
Right Click on Linked Servers--> New Linked Server

Go to security/Linked Servers
Right Click on Linked Servers--> New Linked Server

Use Microsoft OLE DB Provider for ODBC Drivers

Use the Connection String
DRIVER={MySQL ODBC 3.51
Driver};SERVER=myserver.com;DATABASE=database;USER
=user;PASSWORD=password;OPTION=3

And in Provider Options select:

level zero only
Non-transacted updates (something)
Allow InProcess

let me know if you still get an error

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||Still getting the same error :-( BTW - I bookmarked your blog. You have some good stuff there. Thanks for the effort. I will keep trying - Almost no experience with MySQL...

Brad
Ex Nihilo, Nihil Fit
|||

Do you have the MySQL Connector/ODBC 3.51 installed?

get it here-->http://dev.mysql.com/downloads/connector/odbc/3.51.html

also from a query window, does this work for you?

SELECT a.*
FROM OPENROWSET('MSDASQL',
'DRIVER={MySQL ODBC 3.51 Driver};SERVER=192.168.1.99;DATABASE=bug_tracker;user=UserID;PASSWORD=Password;OPTION=3',
TestDB ) AS a

change TestDB to your table name

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||Yes - I have the latest driver installed - the OPENROWSET query give me the same error message as the linked server. I can open the MySQL Query Browser, connect and run queries with no problem. I just cannot seem to get a linked server to work with SQL Server 2000. This is really frustrating. If I can't get this set up, I am going to have to use DTS and write some real heavy-duty scripts as these tables are going to feed a data warehouse in Analysis Services. It also makes me feel pretty stupid because I cannot get it to work. Back to the drawing board and thanks for your help.

Brad
Ex Nihilo, Nihil Fit
|||

The driver is installed on the SQL server box right?

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||Got it fixed finally...Thanks for everyone's help!
|||

I am having a similar problem with SSIS pulling data from MySQL

How did you get it fixed?

Thanks

-Raghu Raman

|||

Hi everybody, I have the same problem that has been treated here, but none of the solutions offered has been good for me.

Does anybody have any help respect to the problem with the linked server?

Thanks, blessings.

Cannot create linked server

I posted this in the Data Access forum already - may have been the wrong place...

I cannot create a linked to a MySql database in Enterprise Manager. Here is the relevant info:

Trying to create a linked server on a SQL Server 2000 Standard

Edition Service Pack 4 running on a Windows 2000 Standard Edition

server.

The MySQL server is running on Red Hat - not sure of the DB or OS

version. However the DSN I created connects to the server correctly.

I can connect using any of the MySQL tools.

I am using the latest ODBC MySQL drive 3.51...Here is the Provider string I am using...

DRIVER={MySQL ODBC 3.51 Driver} ;SERVER=192.168.1.99;DATABASE=bug_tracker; USER=UserID; PASSWORD=Password; OPTION=3

I have also tried using a System DSN to connect with, but that does

not work either. It appears to create the linked server correctly each

time, but I cannot access any of the tables. Here is the error

message I get each time:

Error 7399: OLE DB Provider 'MSDASQL' reported an error. Datasource

name not found and no default driver specified] OLD DB error trace

[OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned

0x80004005: ]

Sure could use a little help!

Thanks!!!!

Brad Feaker

Please reply to forum only! Thanks!

Seems like no one else knows jack about this either!|||

try this

Go to security/Linked Servers
Right Click on Linked Servers--> New Linked Server

Go to security/Linked Servers
Right Click on Linked Servers--> New Linked Server

Use Microsoft OLE DB Provider for ODBC Drivers

Use the Connection String
DRIVER={MySQL ODBC 3.51
Driver};SERVER=myserver.com;DATABASE=database;USER
=user;PASSWORD=password;OPTION=3

And in Provider Options select:

level zero only
Non-transacted updates (something)
Allow InProcess

let me know if you still get an error

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||Still getting the same error :-( BTW - I bookmarked your blog. You have some good stuff there. Thanks for the effort. I will keep trying - Almost no experience with MySQL...

Brad
Ex Nihilo, Nihil Fit|||

Do you have the MySQL Connector/ODBC 3.51 installed?

get it here-->http://dev.mysql.com/downloads/connector/odbc/3.51.html

also from a query window, does this work for you?

SELECT a.*
FROM OPENROWSET('MSDASQL',
'DRIVER={MySQL ODBC 3.51 Driver};SERVER=192.168.1.99;DATABASE=bug_tracker;user=UserID;PASSWORD=Password;OPTION=3',
TestDB ) AS a

change TestDB to your table name

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||Yes - I have the latest driver installed - the OPENROWSET query give me the same error message as the linked server. I can open the MySQL Query Browser, connect and run queries with no problem. I just cannot seem to get a linked server to work with SQL Server 2000. This is really frustrating. If I can't get this set up, I am going to have to use DTS and write some real heavy-duty scripts as these tables are going to feed a data warehouse in Analysis Services. It also makes me feel pretty stupid because I cannot get it to work. Back to the drawing board and thanks for your help.

Brad
Ex Nihilo, Nihil Fit|||

The driver is installed on the SQL server box right?

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||Got it fixed finally...Thanks for everyone's help!|||

I am having a similar problem with SSIS pulling data from MySQL

How did you get it fixed?

Thanks

-Raghu Raman

|||

Hi everybody, I have the same problem that has been treated here, but none of the solutions offered has been good for me.

Does anybody have any help respect to the problem with the linked server?

Thanks, blessings.

Cannot create an instance of OLE DB provider "IBMDADB2" for linked server

System:Win 2003, SQL Server 2005, Using an AD win account that is not a member of the Admin group on the server.

Error message from Management Studio query window:

Msg 7302, Level 16, State 1, Line 1

Cannot create an instance of OLE DB provider "IBMDADB2" for linked server "Sname".

Event messages associated with this error:

App Event ID: 19036

The OLE DB initialization service failed to load. Reinstall Microsoft Data Access Components. If the problem persists, contact product support for the OLEDB provider.

Sys Event ID: 10016

The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID {2206CDB0-19C1-11D1-89E0-00C04FD7A829}

to the user domain\user SID (S-1-5-21-126051702-1034962659-2130403006-7826). This security permission can be modified using the Component Services administrative tool.

I’m getting this error message when trying to run an openquery statement through a linked server to DB2. (SELECT * FROM OPENQUERY(Sname, 'SELECT * FROM tablename’))

The linked server has a remote login and password that it uses to connect to DB2.

I found this from another post on how to fix this error:

Expand Component Services - Computers - My Computer - DCOM Config Select MSDAINITIALIZE Right Click properties then security

Under Security - Launch Permission: enable Local Launch and Local Activation for your SQL Service account

Under Security - Access permissions: Allow System: Local Access and Remote Access.

After completing these steps I still get the error message but the events are no longer generated.I’ve also tried different variations of these steps.The only way I can get this to work is to either make the AD user a member of the Admin group on the server or by putting in a user account that has admin rights to the server in the MSDAINITIALIZE properties – Identity Tab – Run this App as this User.

Can someone please tell me the steps that I’m missing?

Additional info:

If I set the providers allowinprocess to 1 with this,

EXEC master.dbo.sp_MSset_oledb_prop N'IBMDADB2', N'AllowInProcess', 1

It responds with this error:

OLE DB provider "IBMDADB2" for linked server "LName" returned message " SQL10007N Message "0" could not be retrieved. Reason code: "2".

".

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "IBMDADB2" for linked server "LName".

cannot connect/login error message

I am trying to connect to a virtual directory where there is an application
that was developed in .asp.net - it is linked to a sql server database. I am
using IIS, Server 2003 and SQL Server 2000 - same server - Any ideas where
this may be coming from? I posted this message under connect and security
sections. Thanks.
Server Error in '/Admin' Application.
Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information about
the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Login failed for user
'NT AUTHORITY\NETWORK SERVICE'.
Source Error:
An unhandled exception was generated during the execution of the current web
request. Information regarding the origin and location of the exception can
be identified using the exception stack trace below.
Stack Trace:
[SqlException: Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.]
System.Data.SqlClient.ConnectionPool.GetConnection (Boolean&
isInTransaction) +474
System.Data.SqlClient.SqlConnectionPoolManager.Get PooledConnection(SqlConnectionString options, Boolean& isInTransaction) +372
System.Data.SqlClient.SqlConnection.Open() +384
ut.hfrp.asap.DAO.BaseDAO.getConnection() +83
[DAOException: Unable to create connection to database]
ut.hfrp.asap.DAO.BaseDAO.getConnection() +154
ut.hfrp.asap.DAO.UserDAO.GetList() +70
ut.hfrp.asap.BusObj.Settings.AirlineSettings.GetUs erList() +30
ut.hfrp.asap.ManagerApp.Login.SubmitLogin_Click(Ob ject sender, EventArgs
e) +51
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108
System.Web.UI.WebControls.Button.System.Web.UI.IPo stBackEventHandler.RaisePostBackEvent(String eventArgument) +57
System.Web.UI.Page.RaisePostBackEvent(IPostBackEve ntHandler
sourceControl, String eventArgument) +18
System.Web.UI.Page.RaisePostBackEvent(NameValueCol lection postData) +33
System.Web.UI.Page.ProcessRequestMain() +1292
LizW
The ASP service Account is running under Networkservice Account which
is not priviledged in the SQL Server database. Either you change the
authentication in your application to SQL Server authentication
(Checkout the connectionstrings on www.connectionstrings.com for that)
or you change the service account of ASP to a domain or local account
which has to be priviledged in SQL Server.
HTH, Jens Suessmeyer.

cannot connect/login error message

I am trying to connect to a virtual directory where there is an application
that was developed in .asp.net - it is linked to a sql server database. I am
using IIS, Server 2003 and SQL Server 2000 - same server - Any ideas where
this may be coming from? I posted this message under connect and security
sections. Thanks.
Server Error in '/Admin' Application.
----
--
Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information abou
t
the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Login failed for user
'NT AUTHORITY\NETWORK SERVICE'.
Source Error:
An unhandled exception was generated during the execution of the current web
request. Information regarding the origin and location of the exception can
be identified using the exception stack trace below.
Stack Trace:
[SqlException: Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.]
System.Data.SqlClient.ConnectionPool.GetConnection(Boolean&
isInTransaction) +474
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnec
tionString options, Boolean& isInTransaction) +372
System.Data.SqlClient.SqlConnection.Open() +384
ut.hfrp.asap.DAO.BaseDAO.getConnection() +83
[DAOException: Unable to create connection to database]
ut.hfrp.asap.DAO.BaseDAO.getConnection() +154
ut.hfrp.asap.DAO.UserDAO.GetList() +70
ut.hfrp.asap.BusObj.Settings.AirlineSettings.GetUserList() +30
ut.hfrp.asap.ManagerApp.Login.SubmitLogin_Click(Object sender, EventArgs
e) +51
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePo
stBackEvent(String eventArgument) +57
System.Web.UI.Page. RaisePostBackEvent(IPostBackEventHandler
sourceControl, String eventArgument) +18
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain() +1292
LizWThe ASP service Account is running under Networkservice Account which
is not priviledged in the SQL Server database. Either you change the
authentication in your application to SQL Server authentication
(Checkout the connectionstrings on www.connectionstrings.com for that)
or you change the service account of ASP to a domain or local account
which has to be priviledged in SQL Server.
HTH, Jens Suessmeyer.

cannot connect/login error message

I am trying to connect to a virtual directory where there is an application
that was developed in .asp.net - it is linked to a sql server database. I am
using IIS, Server 2003 and SQL Server 2000 - same server - Any ideas where
this may be coming from? I posted this message under connect and security
sections. Thanks.
Server Error in '/Admin' Application
----
Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information about
the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Login failed for user
'NT AUTHORITY\NETWORK SERVICE'.
Source Error:
An unhandled exception was generated during the execution of the current web
request. Information regarding the origin and location of the exception can
be identified using the exception stack trace below.
Stack Trace:
[SqlException: Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.]
System.Data.SqlClient.ConnectionPool.GetConnection(Boolean&
isInTransaction) +474
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) +372
System.Data.SqlClient.SqlConnection.Open() +384
ut.hfrp.asap.DAO.BaseDAO.getConnection() +83
[DAOException: Unable to create connection to database]
ut.hfrp.asap.DAO.BaseDAO.getConnection() +154
ut.hfrp.asap.DAO.UserDAO.GetList() +70
ut.hfrp.asap.BusObj.Settings.AirlineSettings.GetUserList() +30
ut.hfrp.asap.ManagerApp.Login.SubmitLogin_Click(Object sender, EventArgs
e) +51
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler
sourceControl, String eventArgument) +18
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain() +1292
--
LizWThe ASP service Account is running under Networkservice Account which
is not priviledged in the SQL Server database. Either you change the
authentication in your application to SQL Server authentication
(Checkout the connectionstrings on www.connectionstrings.com for that)
or you change the service account of ASP to a domain or local account
which has to be priviledged in SQL Server.
HTH, Jens Suessmeyer.

Cannot connect via Linked Server

We have two servers, each with named instances of SQL Server 2000 SP3a installed. Server1\Instance1 has a linked server to Server2\Instance2. The linked server is using the SQLOLEODB provider. This linked server is setup with remote login and a correct
password. We get an error #17: SQL Server does not exist or access denied. We also get the same behaviour while trying an OPENROWSET query from the Server1\Instance1 Query Analyzer. All other SQL Server instances we have installed (on other machines)
can connect to Server2\Instance2 with a SQLOLEDB linked server, no problems.
As far as I can tell, this error is only happening when using the SQL Server. I can connect to the remote (Server2\Instance2) server using osql from Server1, and I can even connect to Server2\Instance2 by using a SQLOLEDB connection from a desktop app (f
or example, I can test the connection successfully in a UDL using SQLOLEDB to the remote server from Server1).
We are only using named pipes. I have verified that all client and all server network utilities are set exclusively to named pipes. This is true. I have also performed a network trace on the Server1 server. It does not even attempt to contact the Serv
er2 machine. I have also network traced a different SQL Server instance on another machine that can connect to Server2\Instance2 and it's network trace shows a very clear, immediate connection to the Server2.
What other steps can I take to troubleshoot this problem? I think that I have hit all the common trouble points in setting up a linked server.
Thanks,
Frank
Update: We noticed that there was an additional NIC card on the Server1 server that had an APIPA IP address. We have disabled this NIC and now the network trace is showing some communication between Server1 and Server2.
Basically, the trace shows Server1 attempts to connect using SMB to set the session up on Server2. Server2 responds with an NT error 22, STATUS_MORE_PROCESSING_REQUIRED, then a few frames later, it tries to open the IPC$ pipe on Server2. Server2 respond
s with an NT error 34, STATUS_ACCESS_DENIED. So why is SMB not allowing connections to the IPC$ pipe? I can log in to the Server1 machine under the same account as SQL Server is running as and issue a successful "net use \\Server2\IPC$" without a proble
m. The network trace requests from Server1 for the net use look similar to those generated by SQL Server linked server. Why does one get an access denied and the other does not?
Thanks,
Frank
|||Hi Frank,
I learned you are failed to create linked Server between Server1\Instance1
and Server2\Instance2. The error "SQL Server does not exist or access
denied." occurred.
1. Please check the Server Network Utility for named pipes and make sure it
is the default value.
\\.\pipe\MSSQL$ Instance2\sql\query
2. Please check to see if UDP port 1434 is enabled.
3. Please try to add the linked Server using sp_addlinkedserver in QA
(Query Analyzer). What's the result? If there is any error occurring,
please provide the detailed error message.
For more information regarding sp_addlinkedserver, please refer to the
article on SQL Server Books Online.
Topic: "sp_addlinkedserver"
4. Please check the application/system log on both Servers and see if there
is any error information when the "linked Server" error occurred.
5. Please try to create an alias for Server2\Instance2 and then create the
linked Server. Does the same problem still persist?
Also, such issues tend to be complex and take up extensive research time.
I'd like to set your expectations that it may take a while for us to help
you narrow down the problem and we may eventually redirect you to PSS to
continue working with a dedicated Support Professional. If this is
critical, I'd recommend contacting PSS and opening a Support incident
troubleshoot this further. If you need any help in this regard, please let
me know.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
|||Hi Yuan, I have completed the following steps for troubleshooting from your post. My comments are indented below each of your suggestions:
1. Please check the Server Network Utility for named pipes and make sure it
is the default value.
\\.\pipe\MSSQL$ Instance2\sql\query
I have checked the Server & Client network utilities: named pipes is default and the only protocol. That was stated in my original post.
2. Please check to see if UDP port 1434 is enabled.
Yes, port 1434 UDP is listening on the Server2 machine.
3. Please try to add the linked Server using sp_addlinkedserver in QA
(Query Analyzer). What's the result? If there is any error occurring,
please provide the detailed error message.
For more information regarding sp_addlinkedserver, please refer to the
article on SQL Server Books Online.
Topic: "sp_addlinkedserver"
Adding the linked server is not the problem. I can do it either through Enterprise Manager or the stored procedure. It is when I try to connect to the linked server (for example, when EM queries for table names from the linked server) that I get
Error #17 SQL Server does not exist or access denied, as stated in my original post.
4. Please check the application/system log on both Servers and see if there
is any error information when the "linked Server" error occurred.
I have been monitoring the event log (all application, security and system) for any errors on either system and there is nothing.
5. Please try to create an alias for Server2\Instance2 and then create the
linked Server. Does the same problem still persist?
I have tried to create an alias with the same results. No connectivity; access denied.
I have hit most of these normal troubleshooting steps. I don't usually jump to a network trace before trying things like this. The SMB network trace for opening the named pipe shows an access denied error from the Server2. What other issues might cause
this?
Thanks,
Frank
|||Hi Frank,
This issue is very strange. Please try the following steps to see if they
are helpful.
1. Try to create a new linked server using SQL Server directly but not OLE
DB provider for SQL Server. In the meantime, please specify a SQL account
in the general tab to connect to Server2\instance2 server. After creating
this linked server, please try to perform the following sql statements. It
is best to grant the admin permission of server2\instance2 to this test SQL
account.
Select * from OPENQUERY(<Linked Server Name>, 'SQL statements')
Select * from <Linked server Name>X<Table Name>
Does the same error message "SQL Server does not exist or access denied"
still persist? Is there any other error message occurring? It is
appreciated you can provide the completely error message.
2. Please use MDAC checker on your Server1 and provide the result.
HOW TO: Check for MDAC Version
http://support.microsoft.com/default...b;EN-US;301202
I am looking forward to hearing from you soon.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
|||Yuan, we have tried creating a SQL Server without using the OLEDB provider and this failed in the same manner as the prior failures. Both the openquery and the four part fully qualified name queries failed with Error #17, SQL Server does not exist or acc
ess denied.
The MDAC is 2.7SP1 on Server1, 2.8 on Server2...
Any help at this point would be greatly appreciated...
Thanks,
Frank
-- "Yuan Shao" wrote: --
Hi Frank,
This issue is very strange. Please try the following steps to see if they
are helpful.
1. Try to create a new linked server using SQL Server directly but not OLE
DB provider for SQL Server. In the meantime, please specify a SQL account
in the general tab to connect to Server2\instance2 server. After creating
this linked server, please try to perform the following sql statements. It
is best to grant the admin permission of server2\instance2 to this test SQL
account.
Select * from OPENQUERY(<Linked Server Name>, 'SQL statements')
Select * from <Linked server Name>?X<Table Name>
Does the same error message "SQL Server does not exist or access denied"
still persist? Is there any other error message occurring? It is
appreciated you can provide the completely error message.
2. Please use MDAC checker on your Server1 and provide the result.
HOW TO: Check for MDAC Version
http://support.microsoft.com/default...b;EN-US;301202
I am looking forward to hearing from you soon.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
|||Hi Frank,
Thanks for your feedback. After reviewing our discussion in detail, I
noticed you test the connection successfully in a UDL using SQLOLEDB to the
remote server (server2/instance2) from Server1. However, you still got the
error "SQL Server does not exist or access denied." using linked server
I would like you to check if this is a permission problem. Please try to
create a linked server with sa login and check to see if the same error
persists. I have attached a linkedserver.zip file with my notification to
help describe the configuration of linked server using sa login.
I am looking forward to hearing from you soon.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
|||Yuan, we have tried connecting to the linked server using the sa password and it still results in the original failure: Error 17, SQL Server does not exist or access denied. It seems like it is the named pipes connectivity shutting the door down rather t
han SQL Server, although I cannot verify this as neither SQL Server nor the operating system are logging this (despite having all failure logging turned on both the OS local security policy and in SQL Server). Any other ideas?
Thanks,
Frank
|||Have you tried a connection method other than named pipes. Try making TcpIP and secondary connection method and setting up an alias using IP.
Are both servers on the same segment?
Sometimes netbios names are not passed across segments.
Have you tried rebooting the server1 since disabling the NIC. Sometimes it needs to rebroadcast its existance on the NIC.
Thanks
Jeff
|||Hello Frank,
Looking at the nature of this issue, it would require intensive
troubleshooting which would be done quickly and effectively with direct
assistance from a Microsoft Support Professional through Microsoft Product
Support Services. You can contact Microsoft Product Support directly to
discuss additional support options you may have available, by contacting us
at 1-(800)936-5800 or by choosing one of the options listed at
http://support.microsoft.com/default...=sz;en-us;top.
If this is not an urgent issue and your would like us to create an incident
for you and have Microsoft Customer Service Representative contact you
directly, please send email to (remove "online." from this no Spam email
address): mailto:dscommhf@.online.microsoft.com with the following
information,
*Include "Followup: <Tomcat IssueID>" in the email Subject.
*Location of the post
*Subject Line
*First Name, Last Name
*MSDN Subscriber ID
*Company name (if any)
*Phone number
*e-mail address
Thanks for using MSDN Newsgroups.
Vikrant Dalwale
Microsoft SQL Server Support Professional
This posting is provided "AS IS" with no warranties, and confers no rights.
Get secure !! For info, please visit http://www.microsoft.com/security.
Please reply to Newsgroups only.
| Thread-Topic: Cannot connect via Linked Server
| thread-index: AcQh0Df+wj3plzMnQ3+eAqcmfTrgkg==
| X-WN-Post: microsoft.public.sqlserver.connect
| From: "=?Utf-8?B?RnJhbmsgSm9uZXM=?=" <frank@.bogusfiosincfake.com>
| References: <99E987D2-0636-4917-8627-8E1A87789198@.microsoft.com>
<4B4AE818-15A4-4C50-97DC-7370D8DC8D49@.microsoft.com>
<3tFzedKHEHA.612@.cpmsftngxa06.phx.gbl>
| Subject: RE: Cannot connect via Linked Server
| Date: Tue, 13 Apr 2004 20:26:04 -0700
| Lines: 4
| Message-ID: <50E78236-79B1-4D3F-9BFE-CB02692DB966@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.connect
| Path: cpmsftngxa06.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.connect:40978
| NNTP-Posting-Host: tk2msftcmty1.phx.gbl 10.40.1.180
| X-Tomcat-NG: microsoft.public.sqlserver.connect
|
| Yuan, we have tried connecting to the linked server using the sa password
and it still results in the original failure: Error 17, SQL Server does not
exist or access denied. It seems like it is the named pipes connectivity
shutting the door down rather than SQL Server, although I cannot verify
this as neither SQL Server nor the operating system are logging this
(despite having all failure logging turned on both the OS local security
policy and in SQL Server). Any other ideas?
Thanks,
Frank
|

Cannot connect via Linked Server

We have two servers, each with named instances of SQL Server 2000 SP3a insta
lled. Server1\Instance1 has a linked server to Server2\Instance2. The link
ed server is using the SQLOLEODB provider. This linked server is setup with
remote login and a correct
password. We get an error #17: SQL Server does not exist or access denied.
We also get the same behaviour while trying an OPENROWSET query from the Se
rver1\Instance1 Query Analyzer. All other SQL Server instances we have inst
alled (on other machines)
can connect to Server2\Instance2 with a SQLOLEDB linked server, no problems.
As far as I can tell, this error is only happening when using the SQL Server
. I can connect to the remote (Server2\Instance2) server using osql from Se
rver1, and I can even connect to Server2\Instance2 by using a SQLOLEDB conne
ction from a desktop app (f
or example, I can test the connection successfully in a UDL using SQLOLEDB t
o the remote server from Server1).
We are only using named pipes. I have verified that all client and all serv
er network utilities are set exclusively to named pipes. This is true. I h
ave also performed a network trace on the Server1 server. It does not even
attempt to contact the Serv
er2 machine. I have also network traced a different SQL Server instance on
another machine that can connect to Server2\Instance2 and it's network trace
shows a very clear, immediate connection to the Server2.
What other steps can I take to troubleshoot this problem? I think that I ha
ve hit all the common trouble points in setting up a linked server.
Thanks,
FrankUpdate: We noticed that there was an additional NIC card on the Server1 serv
er that had an APIPA IP address. We have disabled this NIC and now the netw
ork trace is showing some communication between Server1 and Server2.
Basically, the trace shows Server1 attempts to connect using SMB to set the
session up on Server2. Server2 responds with an NT error 22, STATUS_MORE_PR
OCESSING_REQUIRED, then a few frames later, it tries to open the IPC$ pipe o
n Server2. Server2 respond
s with an NT error 34, STATUS_ACCESS_DENIED. So why is SMB not allowing con
nections to the IPC$ pipe? I can log in to the Server1 machine under the sa
me account as SQL Server is running as and issue a successful "net use \\Ser
ver2\IPC$" without a proble
m. The network trace requests from Server1 for the net use look similar to
those generated by SQL Server linked server. Why does one get an access den
ied and the other does not?
Thanks,
Frank|||Hi Frank,
I learned you are failed to create linked Server between Server1\Instance1
and Server2\Instance2. The error "SQL Server does not exist or access
denied." occurred.
1. Please check the Server Network Utility for named pipes and make sure it
is the default value.
\\.\pipe\MSSQL$ Instance2\sql\query
2. Please check to see if UDP port 1434 is enabled.
3. Please try to add the linked Server using sp_addlinkedserver in QA
(Query Analyzer). What's the result? If there is any error occurring,
please provide the detailed error message.
For more information regarding sp_addlinkedserver, please refer to the
article on SQL Server Books Online.
Topic: "sp_addlinkedserver"
4. Please check the application/system log on both Servers and see if there
is any error information when the "linked Server" error occurred.
5. Please try to create an alias for Server2\Instance2 and then create the
linked Server. Does the same problem still persist?
Also, such issues tend to be complex and take up extensive research time.
I'd like to set your expectations that it may take a while for us to help
you narrow down the problem and we may eventually redirect you to PSS to
continue working with a dedicated Support Professional. If this is
critical, I'd recommend contacting PSS and opening a Support incident
troubleshoot this further. If you need any help in this regard, please let
me know.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||Hi Yuan, I have completed the following steps for troubleshooting from your
post. My comments are indented below each of your suggestions:
1. Please check the Server Network Utility for named pipes and make sure it
is the default value.
\\.\pipe\MSSQL$ Instance2\sql\query
I have checked the Server & Client network utilities: named pipes is default
and the only protocol. That was stated in my original post.
2. Please check to see if UDP port 1434 is enabled.
Yes, port 1434 UDP is listening on the Server2 machine.
3. Please try to add the linked Server using sp_addlinkedserver in QA
(Query Analyzer). What's the result? If there is any error occurring,
please provide the detailed error message.
For more information regarding sp_addlinkedserver, please refer to the
article on SQL Server Books Online.
Topic: "sp_addlinkedserver"
Adding the linked server is not the problem. I can do it either through Ent
erprise Manager or the stored procedure. It is when I try to connect to the
linked server (for example, when EM queries for table names from the linked
server) that I get
Error #17 SQL Server does not exist or access denied, as stated in my origin
al post.
4. Please check the application/system log on both Servers and see if there
is any error information when the "linked Server" error occurred.
I have been monitoring the event log (all application, security and system)
for any errors on either system and there is nothing.
5. Please try to create an alias for Server2\Instance2 and then create the
linked Server. Does the same problem still persist?
I have tried to create an alias with the same results. No connectivity; acc
ess denied.
I have hit most of these normal troubleshooting steps. I don't usually jump
to a network trace before trying things like this. The SMB network trace f
or opening the named pipe shows an access denied error from the Server2. Wh
at other issues might cause
this?
Thanks,
Frank|||Hi Frank,
This issue is very strange. Please try the following steps to see if they
are helpful.
1. Try to create a new linked server using SQL Server directly but not OLE
DB provider for SQL Server. In the meantime, please specify a SQL account
in the general tab to connect to Server2\instance2 server. After creating
this linked server, please try to perform the following sql statements. It
is best to grant the admin permission of server2\instance2 to this test SQL
account.
Select * from OPENQUERY(<Linked Server Name>, 'SQL statements')
Select * from <Linked server Name>_<Table Name>
Does the same error message "SQL Server does not exist or access denied"
still persist? Is there any other error message occurring? It is
appreciated you can provide the completely error message.
2. Please use MDAC checker on your Server1 and provide the result.
HOW TO: Check for MDAC Version
http://support.microsoft.com/defaul...kb;EN-US;301202
I am looking forward to hearing from you soon.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||Yuan, we have tried creating a SQL Server without using the OLEDB provider a
nd this failed in the same manner as the prior failures. Both the openquery
and the four part fully qualified name queries failed with Error #17, SQL S
erver does not exist or acc
ess denied.
The MDAC is 2.7SP1 on Server1, 2.8 on Server2...
Any help at this point would be greatly appreciated...
Thanks,
Frank
-- "Yuan Shao" wrote: --
Hi Frank,
This issue is very strange. Please try the following steps to see if they
are helpful.
1. Try to create a new linked server using SQL Server directly but not OLE
DB provider for SQL Server. In the meantime, please specify a SQL account
in the general tab to connect to Server2\instance2 server. After creating
this linked server, please try to perform the following sql statements. It
is best to grant the admin permission of server2\instance2 to this test SQL
account.
Select * from OPENQUERY(<Linked Server Name>, 'SQL statements')
Select * from <Linked server Name>?_<Table Name>
Does the same error message "SQL Server does not exist or access denied"
still persist? Is there any other error message occurring? It is
appreciated you can provide the completely error message.
2. Please use MDAC checker on your Server1 and provide the result.
HOW TO: Check for MDAC Version
http://support.microsoft.com/defaul...kb;EN-US;301202
I am looking forward to hearing from you soon.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||Hi Frank,
Thanks for your feedback. After reviewing our discussion in detail, I
noticed you test the connection successfully in a UDL using SQLOLEDB to the
remote server (server2/instance2) from Server1. However, you still got the
error "SQL Server does not exist or access denied." using linked server
I would like you to check if this is a permission problem. Please try to
create a linked server with sa login and check to see if the same error
persists. I have attached a linkedserver.zip file with my notification to
help describe the configuration of linked server using sa login.
I am looking forward to hearing from you soon.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||Yuan, we have tried connecting to the linked server using the sa password an
d it still results in the original failure: Error 17, SQL Server does not ex
ist or access denied. It seems like it is the named pipes connectivity shut
ting the door down rather t
han SQL Server, although I cannot verify this as neither SQL Server nor the
operating system are logging this (despite having all failure logging turned
on both the OS local security policy and in SQL Server). Any other ideas?
Thanks,
Frank|||Have you tried a connection method other than named pipes. Try making TcpIP
and secondary connection method and setting up an alias using IP.
Are both servers on the same segment?
Sometimes netbios names are not passed across segments.
Have you tried rebooting the server1 since disabling the NIC. Sometimes it
needs to rebroadcast its existance on the NIC.
Thanks
Jeff|||Hello Frank,
Looking at the nature of this issue, it would require intensive
troubleshooting which would be done quickly and effectively with direct
assistance from a Microsoft Support Professional through Microsoft Product
Support Services. You can contact Microsoft Product Support directly to
discuss additional support options you may have available, by contacting us
at 1-(800)936-5800 or by choosing one of the options listed at
http://support.microsoft.com/defaul...d=sz;en-us;top.
If this is not an urgent issue and your would like us to create an incident
for you and have Microsoft Customer Service Representative contact you
directly, please send email to (remove "online." from this no Spam email
address): mailto:dscommhf@.online.microsoft.com with the following
information,
*Include "Followup: <Tomcat IssueID>" in the email Subject.
*Location of the post
*Subject Line
*First Name, Last Name
*MSDN Subscriber ID
*Company name (if any)
*Phone number
*e-mail address
Thanks for using MSDN Newsgroups.
Vikrant Dalwale
Microsoft SQL Server Support Professional
This posting is provided "AS IS" with no warranties, and confers no rights.
Get secure !! For info, please visit http://www.microsoft.com/security.
Please reply to Newsgroups only.
| Thread-Topic: Cannot connect via Linked Server
| thread-index: AcQh0Df+wj3plzMnQ3+eAqcmfTrgkg==
| X-WN-Post: microsoft.public.sqlserver.connect
| From: "examnotes" <frank@.bogusfiosincfake.com>
| References: <99E987D2-0636-4917-8627-8E1A87789198@.microsoft.com>
<4B4AE818-15A4-4C50-97DC-7370D8DC8D49@.microsoft.com>
<3tFzedKHEHA.612@.cpmsftngxa06.phx.gbl>
| Subject: RE: Cannot connect via Linked Server
| Date: Tue, 13 Apr 2004 20:26:04 -0700
| Lines: 4
| Message-ID: <50E78236-79B1-4D3F-9BFE-CB02692DB966@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.connect
| Path: cpmsftngxa06.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.connect:40978
| NNTP-Posting-Host: tk2msftcmty1.phx.gbl 10.40.1.180
| X-Tomcat-NG: microsoft.public.sqlserver.connect
|
| Yuan, we have tried connecting to the linked server using the sa password
and it still results in the original failure: Error 17, SQL Server does not
exist or access denied. It seems like it is the named pipes connectivity
shutting the door down rather than SQL Server, although I cannot verify
this as neither SQL Server nor the operating system are logging this
(despite having all failure logging turned on both the OS local security
policy and in SQL Server). Any other ideas?
Thanks,
Frank
|

Cannot connect to remote distributor -- linked server error

I have 2 servers: #1 -- SQL 2005 SP1 publisher ; #2 -- SQL 2005 SP2 subscriber

originally I had #1 as pub and dist but dist but killing my CPU so I was in the process of moving the dist to #2... Got it all configured and when I tried to add #2 as a dist for #1 it fails when I get to the administrative password screen... It give me an error about how it cannot connect with the given password but under that is says linked server failed.

Since it mentioned linked server I tried to connect via the previous linked server to #2 and it failed... I cannot connect to #2 anymore from #1. I can login directly to #2 and I can use osql to connect to #2 but linked server does not work. I tried all drivers and many configurations...

Any ideas?

Some updates...

I can put the distributor locally on #1 and create both push and pull subscriptions to #2.

Cannot connect to 2000 from 2005 via linked server

Interesting issue.. I think

I have 2 linked servers on my dev 2005 instance (sp1-win 2003). My 2005 instance shares the physical server with a 2000 instance. This linked server works fine. The second linked server is on a separate server running sql 2000 sp3. I cannot connect to this server at all. I can connect the other way -- from the 2000 to the 2005. I have tried all kinds of combinations of security and options but nothing works. The only resolutions I have found referenced installing Management Studio on the 2000 server or creating an ODBC connection from the 2005 instance. Any ideas?

Here is the error I get:

OLE DB provider "SQLNCLI" for linked server "ALLFLDB6" returned message "Login timeout expired".

OLE DB provider "SQLNCLI" for linked server "ALLFLDB6" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".

Msg 10061, Level 16, State 1, Line 0

TCP Provider: No connection could be made because the target machine actively refused it.

To make sure I understand, you got box A running Win2K3 SP1 that has a SQL2K5 instance (inst1) and a SQL2K instance (inst2). You've got another box B that has a SQLK2K5 instance (inst3). Inst1 has linked server entries for inst2 and inst3. Inst3 has linked server entries for inst1. The problem is that from inst1 connecting to inst3 isn't working.

What is the OS of box B? It sounds like you may have a firewall running on box B preventing connectivity. Also, is the TCP protocol enabled on inst3?

Here's a link to a bunch of connectivity troubleshooting tips:
http://blogs.msdn.com/sql_protocols/archive/2005/09/28/474698.aspx

Hope this helps,
Vaughn

|||

Close.... but not right...

Box 1 -- Windows 2003 SP1, SQL 2005 SP1 (inst2) and SQL 2000 SP3 (inst1)

Box 2 -- Windows 2003 SP1, SQL 2000 SP3 (inst3)

So inst2 has a linked server to inst1 and inst3. It can talk to inst1 with no problem but gets the error posted above when talking to inst3. Inst3 can talk to inst1 and inst2 with no issues. also inst1 can talk to inst3 with no issues. It does it every day so I know it has nothing to do with the physical servers (hopefully).

|||

I solved the issue.. at least on a temporary basis. I created an ODBC connection using the SQL 2000 driver. It is not that fast but I am only selecting from a table with only a couple hundred records...

If anything else comes up please let me know.