Solved

Error with ASP.NET 2.0 page running a linked server query

Posted on 2007-11-14
10
306 Views
Last Modified: 2011-09-20
I have a query i want to run via my asp webpage. It runs fine in SQL Server 2005. It's based on a linked server. However when I tried to throw this query up in ASP. I get the following error. I need someone to help me figure out what this means in plain engrish. I don't really know what to do...

The OLE DB provider "SQLNCLI" for linked server "IN-MMGE-CELLLAB" reported an error. Authentication failed.
Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "IN-MMGE-CELLLAB".

Stack Trace:


[SqlException (0x80131904): The OLE DB provider "SQLNCLI" for linked server "IN-MMGE-CELLLAB" reported an error. Authentication failed.
Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "IN-MMGE-CELLLAB".]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +862234
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +739110
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
   System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1956
   System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +31
   System.Data.SqlClient.SqlDataReader.get_MetaData() +62
   System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +297
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +903
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +132
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +122
   System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +12
   System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +7
   System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +141
   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137
   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83
   System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1770
   System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +17
   System.Web.UI.WebControls.DataBoundControl.PerformSelect() +149
   System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70
   System.Web.UI.WebControls.GridView.DataBind() +4
   System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82
   System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +69
   System.Web.UI.Control.EnsureChildControls() +87
   System.Web.UI.Control.PreRenderRecursiveInternal() +41
   System.Web.UI.Control.PreRenderRecursiveInternal() +161
   System.Web.UI.Control.PreRenderRecursiveInternal() +161
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1360

 
0
Comment
Question by:edi77
  • 5
  • 5
10 Comments
 
LVL 37

Assisted Solution

by:samtran0331
samtran0331 earned 500 total points
Comment Utility
I don't think the problem is because you're using a linked server, it's a problem with the connection string, does your connection string include a username/password with access to the db server?
0
 

Author Comment

by:edi77
Comment Utility
no i think its using the same connection string i used for other tables in the website. how would the syntax look?
0
 
LVL 37

Assisted Solution

by:samtran0331
samtran0331 earned 500 total points
Comment Utility
0
 
LVL 37

Assisted Solution

by:samtran0331
samtran0331 earned 500 total points
Comment Utility
0
 

Author Comment

by:edi77
Comment Utility
you meanthe connection string  from asp to the query right because the query works between the linked sql server instances
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 37

Assisted Solution

by:samtran0331
samtran0331 earned 500 total points
Comment Utility
>>you meanthe connection string  from asp to the query right

yes, because if you're using sql2005 management studio, you're logging into the db servers with either your windows username/password (in which case the app is not using the same) or a username/password that exists in the db servers (which should be part of your app's conn string)...
0
 

Author Comment

by:edi77
Comment Utility
but im using windows authentication so im logging into the app with my network password.

so i cant put that in the connection string for everyone who will use the app....

how would that look? not sure exactly what you mean can u give me an example of the syntax in the connection string u mean?
0
 
LVL 37

Assisted Solution

by:samtran0331
samtran0331 earned 500 total points
Comment Utility
>>but im using windows authentication so im logging into the app with my network password
That gets you into the application, but unless you are using impersonation, the app is still using the default account NETWORK SERVICE (IIS6), if lower version of IIS, it's using the account ASPNET.
So when the app communicates with other servers (like the DB server) it is using one of those accounts.
Usually what I do, is create a user on the DB server(s) called "webapps" and then any connection string a web app(s) uses, I use the username and password for the "webapps" db account.
What connectionstring do you have currently?
0
 

Author Comment

by:edi77
Comment Utility
ok i actually am using impersonation actually.  do u use windows authentication? or forms when you create your webapps person? cuz im using windows auth.

can u show me an example of the syntax u use for your conn string?

all i did for my connection string was use gridview and bind the query(view) using the same connection string i set for the rest of the tables and in the web config. of course these others are not using a linked server.

this is what my web config looks like
<connectionStrings>
  <add name="TestADNIConnectionString" connectionString="Data Source=IN-MMGE-HLOWRLD;Initial Catalog=TestADNI;Integrated Security=True"
   providerName="System.Data.SqlClient" />
 </connectionStrings>
      <system.web>
              
  <roleManager enabled="false" />
            <identity impersonate="true"/>
  <customErrors mode="Off"/>


            <compilation debug="true"/>
            <!--
            The <authentication> section enables configuration
            of the security authentication mode used by
            ASP.NET to identify an incoming user.
        -->
      
            <authentication mode="Windows"/>
            <authorization>
            
                  <allow roles="ADS\In-mmge-hd-admins"/>
                  <allow roles="ADS\In-mmge-hd-users"/>
                  
                  <deny users="*"/>
                  
            </authorization>
0
 

Accepted Solution

by:
edi77 earned 0 total points
Comment Utility
ok i actually am using impersonation actually.  do u use windows authentication? or forms when you create your webapps person? cuz im using windows auth.

can u show me an example of the syntax u use for your conn string?

all i did for my connection string was use gridview and bind the query(view) using the same connection string i set for the rest of the tables and in the web config. of course these others are not using a linked server.

this is what my web config looks like
<connectionStrings>
  <add name="TestADNIConnectionString" connectionString="Data Source=IN-MMGE-HLOWRLD;Initial Catalog=TestADNI;Integrated Security=True"
   providerName="System.Data.SqlClient" />
 </connectionStrings>
      <system.web>
              
  <roleManager enabled="false" />
            <identity impersonate="true"/>
  <customErrors mode="Off"/>


            <compilation debug="true"/>
            <!--
            The <authentication> section enables configuration
            of the security authentication mode used by
            ASP.NET to identify an incoming user.
        -->
      
            <authentication mode="Windows"/>
            <authorization>
            
                  <allow roles="ADS\In-mmge-hd-admins"/>
                  <allow roles="ADS\In-mmge-hd-users"/>
                  
                  <deny users="*"/>
                  
            </authorization>
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now