Report connection fails with DNS name

Hi,

I am facing problem while setting connection string in Report Service project.
Problem is when I specify DNS name instead of machine name as the Server in the connection string.

When I specify DNS name in the connection string it gives error that "A connection cannot be made.Ensure that the server is running" on click of "Test Connection"
Also I am not able to view reports directly through IE. I.e. http://<DNS_NAME>/Reports.
When I access Reports, it prompts me to enter username/password. Even after specifying valid username, it shows me the prompt again n again.

Is there some known issue with Reporting Service or with IIS setting not able to connect to a DNS name? I did not face with any issue when
I specified DNS name in the connection string of Analysis Service project.

Did anyone encountered DNS related problem with SQL Server 2005 Reporting Service?

We also verified that:
1: Ping with both machine name and DNS name returns same result.
2: nslookup with both machine name and DNS name resolves to same IP.

3: Tried putting the DNS name in the hosts file.

Eagerly awaiting some positive reply.

Note: I am working with SQL Server 2005 Standard Edition.

No replies yet

We are stuck up with this issue since quite some time.

still waiting for some positive inputs.

|||

After doing some investigation, I have found that the issue is with Analysis server not able to connect using the DNS name (Note: my Reporting Service proj is connecting to Analysis services datasource). When I connect to Analysis server through SQL Server management studio (by def which uses Win authentication), and specify machine name (ABC) in Server then it connects but if I give DNS name (XYZ.PWR.COM) it gives me the following error:

************************************************************************************************************
A connection cannot be made. Ensure that the server is running. (Microsoft.AnalysisServices.AdomdClient)

Program Location:

at Microsoft.AnalysisServices.AdomdClient.XmlaClient.Connect(ConnectionInfo connectionInfo, Boolean beginSession)
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Microsoft.AnalysisServices.AdomdClient.AdomdConnection.IXmlaClientProviderEx.ConnectXmla()
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.ConnectToXMLA(Boolean createSession, Boolean isHTTP)
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.Open()
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server)
at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

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

Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host. (System)

Program Location:

at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size)
at System.IO.BufferedStream.Read(Byte[] array, Int32 offset, Int32 count)
at Microsoft.AnalysisServices.AdomdClient.DimeRecord.ForceRead(Stream stream, Byte[] buffer, Int32 length)
at Microsoft.AnalysisServices.AdomdClient.DimeRecord.ReadHeader()
at Microsoft.AnalysisServices.AdomdClient.DimeRecord..ctor(Stream stream)
at Microsoft.AnalysisServices.AdomdClient.DimeReader.ReadRecord()
at Microsoft.AnalysisServices.AdomdClient.TcpStream.GetResponseDataType()

************************************************************************************************************

If I connect to Reporting Service using DNS name then I get the following error:

************************************************************************************************************
===================================

Cannot connect to XYZ.PWR.COM.

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

A connection to the report server failed. Most likely, you do not have permission to connect to the computer. You should either try to connect using a report server URL for the Server name, or if the Distributed COM Users group exists on the remote computer, verify that you are a member. (Microsoft.SqlServer.Management.UI.RSClient)

Program Location:

at Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.ParseServerName(String serverName)
at Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.RSTypeImpl.CreateSqlOlapConnectionInfoBase(UIConnectionInfo ci)
at Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.RSTypeImpl.GetConnectionObject(UIConnectionInfo ci)
at Microsoft.SqlServer.Management.UI.ConnectionDlg.RSType.GetConnectionObject(UIConnectionInfo ci)
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server)
at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()
************************************************************************************************************

When I ping with machine name (ABC) and DNS name (XYZ.PWR.COM), both resolves to the same IP.
C:\Documents and Settings\Administrator>ping ABC

Pinging ABC.corp.abctech.com [xx.x.xx.xx] with 32 bytes of d

C:\Documents and Settings\Administrator>ping XYZ.PWR.COM

Pinging XYZ.PWR.COM [xx.x.xx.xx] with 32 bytes of data:

Is this problem because of the kerberos authentication that Analysis server uses to authenticate?

I am able to connect to the Database engine using DNS name (but using SQL Server authentication).
Also I was able to view the reportserver and reports page by changing the IIS authentication to NTLM.
But finally I am not able to view the reports.

Can anyone please help me in solving this issue.

Awatiitng some positive reply.

Report connection fails with DNS name

Hi,

I am facing problem while setting connection string in Report Service project.
Problem is when I specify DNS name instead of machine name as the Server in the connection string.

When I specify DNS name in the connection string it gives error that "A connection cannot be made.Ensure that the server is running" on click of "Test Connection"
Also I am not able to view reports directly through IE. I.e. http://<DNS_NAME>/Reports.
When I access Reports, it prompts me to enter username/password. Even after specifying valid username, it shows me the prompt again n again.

Is there some known issue with Reporting Service or with IIS setting not able to connect to a DNS name? I did not face with any issue when
I specified DNS name in the connection string of Analysis Service project.

Did anyone encountered DNS related problem with SQL Server 2005 Reporting Service?

We also verified that:
1: Ping with both machine name and DNS name returns same result.
2: nslookup with both machine name and DNS name resolves to same IP.

3: Tried putting the DNS name in the hosts file.

Eagerly awaiting some positive reply.

Note: I am working with SQL Server 2005 Standard Edition.

No replies yet

We are stuck up with this issue since quite some time.

still waiting for some positive inputs.

|||

After doing some investigation, I have found that the issue is with Analysis server not able to connect using the DNS name (Note: my Reporting Service proj is connecting to Analysis services datasource). When I connect to Analysis server through SQL Server management studio (by def which uses Win authentication), and specify machine name (ABC) in Server then it connects but if I give DNS name (XYZ.PWR.COM) it gives me the following error:

************************************************************************************************************
A connection cannot be made. Ensure that the server is running. (Microsoft.AnalysisServices.AdomdClient)

Program Location:

at Microsoft.AnalysisServices.AdomdClient.XmlaClient.Connect(ConnectionInfo connectionInfo, Boolean beginSession)
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Microsoft.AnalysisServices.AdomdClient.AdomdConnection.IXmlaClientProviderEx.ConnectXmla()
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.ConnectToXMLA(Boolean createSession, Boolean isHTTP)
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.Open()
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server)
at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

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

Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host. (System)

Program Location:

at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size)
at System.IO.BufferedStream.Read(Byte[] array, Int32 offset, Int32 count)
at Microsoft.AnalysisServices.AdomdClient.DimeRecord.ForceRead(Stream stream, Byte[] buffer, Int32 length)
at Microsoft.AnalysisServices.AdomdClient.DimeRecord.ReadHeader()
at Microsoft.AnalysisServices.AdomdClient.DimeRecord..ctor(Stream stream)
at Microsoft.AnalysisServices.AdomdClient.DimeReader.ReadRecord()
at Microsoft.AnalysisServices.AdomdClient.TcpStream.GetResponseDataType()

************************************************************************************************************

If I connect to Reporting Service using DNS name then I get the following error:

************************************************************************************************************
===================================

Cannot connect to XYZ.PWR.COM.

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

A connection to the report server failed. Most likely, you do not have permission to connect to the computer. You should either try to connect using a report server URL for the Server name, or if the Distributed COM Users group exists on the remote computer, verify that you are a member. (Microsoft.SqlServer.Management.UI.RSClient)

Program Location:

at Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.ParseServerName(String serverName)
at Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.RSTypeImpl.CreateSqlOlapConnectionInfoBase(UIConnectionInfo ci)
at Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.RSTypeImpl.GetConnectionObject(UIConnectionInfo ci)
at Microsoft.SqlServer.Management.UI.ConnectionDlg.RSType.GetConnectionObject(UIConnectionInfo ci)
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server)
at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()
************************************************************************************************************

When I ping with machine name (ABC) and DNS name (XYZ.PWR.COM), both resolves to the same IP.
C:\Documents and Settings\Administrator>ping ABC

Pinging ABC.corp.abctech.com [xx.x.xx.xx] with 32 bytes of d

C:\Documents and Settings\Administrator>ping XYZ.PWR.COM

Pinging XYZ.PWR.COM [xx.x.xx.xx] with 32 bytes of data:

Is this problem because of the kerberos authentication that Analysis server uses to authenticate?

I am able to connect to the Database engine using DNS name (but using SQL Server authentication).
Also I was able to view the reportserver and reports page by changing the IIS authentication to NTLM.
But finally I am not able to view the reports.

Can anyone please help me in solving this issue.

Awatiitng some positive reply.

Report Builder error.

Hi,

I am facing a peculiar situation where my ad-hoc report is inter-mittently giving me the following error based on the parameter values I am selecting from the dropdown. What could be the possible reasons for this ?

---

Report execution error
The report might not be valid or the server could not process the data.

An existing connection was forcibly closed by the remote host.
-
Unable to read data from the transport connection:An existing connection was forcibly closed by the remote host.
-
The underlying connection was closed: An unexpected error occured on a receive.

---

In the same report if I try to add another runtime filter, the application crashes. This filter field has about 50k available values.Is that the reason for such behaviour? What can be the possible solution here ?

Is this error displayed during execution (preview) of the report or during design-time?

If it is being displayed during design-time, try opening the model in Model Designer and change the element's "Value Selection" property to "None". This will tell Report Builder to use a textbox instead of a dropdown menu for this element.

If this error is during execution (preview) of the report, does it occur when the report is published and executed directly from the server?

HTH, Jason Tremper

|||

Hi !

Sorry for the late response !The error is shown during execution of the ad-hoc report . As I said the above error comes only if I select some particular set of values from the parameter dropdowns.
The other question is that of the filter condition.The application crashes during the design time when I try to apply that filter.This filter probably tries to load all the available values which is about 50k!
I cannot make the 'Value Selection' property to 'None'. The user won't have a clue as to what to enter in the text box.

Report Builder error.

Hi,

I am facing a peculiar situation where my ad-hoc report is inter-mittently giving me the following error based on the parameter values I am selecting from the dropdown. What could be the possible reasons for this ?

---

Report execution error
The report might not be valid or the server could not process the data.

An existing connection was forcibly closed by the remote host.
-
Unable to read data from the transport connection:An existing connection was forcibly closed by the remote host.
-
The underlying connection was closed: An unexpected error occured on a receive.

---

In the same report if I try to add another runtime filter, the application crashes. This filter field has about 50k available values.Is that the reason for such behaviour? What can be the possible solution here ?

Is this error displayed during execution (preview) of the report or during design-time?

If it is being displayed during design-time, try opening the model in Model Designer and change the element's "Value Selection" property to "None". This will tell Report Builder to use a textbox instead of a dropdown menu for this element.

If this error is during execution (preview) of the report, does it occur when the report is published and executed directly from the server?

HTH, Jason Tremper

|||

Hi !

Sorry for the late response !The error is shown during execution of the ad-hoc report . As I said the above error comes only if I select some particular set of values from the parameter dropdowns.
The other question is that of the filter condition.The application crashes during the design time when I try to apply that filter.This filter probably tries to load all the available values which is about 50k!
I cannot make the 'Value Selection' property to 'None'. The user won't have a clue as to what to enter in the text box.

Reply to Importing Error

Hi all,

I am facing problem on importing csv data file into table,

For Ex:

csv file which has 2 columns & 4 records are as follows

EmpNo EmpName

1 a
2 b
3 c
1 a

I created new table called T4, structure of T4 is

EmpNo Varchar(50) with Primary Key
EmpName Varchar(50) disabled Allow Nulls

Now I started importing csv data to T4 table, during importing it couldn't finish entire process. Throwing error, when I remove primary key with disable Allow Nulls for both constraints of a table T4. Importing data successfull.
My question is if I have above said structure for the table T4 (with primary Key), during importing instead of throwing error let it be import first 3 rows then throw error as primary key enabled could not able to import. Is this can be possibe, if so please suggest me to solve the problem.Can I use Bulk Insert or SQL Scripts to solve this problem.

Thanks in advance
Karna

I believe that I understand your current situation, but I do not think I understand what it is you are trying to do.

If you're trying to insert the three "good rows" and "fail" the one "bad" row without failing the package as a whole, you may be able to accomplish this by adding an error output to the destination to which you're writing these records. Then the "bad" records will be redirected to that portion of the data flow, where you can count them, log them, or ignore them if you want.

Is this what you're trying to accomplish? If so, please let us know if this technique gives you the solution you need. If not, please see if you can rephrase your goal, and I can try again.

|||Hi Mathew,

Thanks for your reply. I am executing wizard in SQL Server 2005. Actual problem is "I am preparing a job which can import CSV data file to the table. Table already has primary key & disabled allow null option. Here in CSV file it has duplicate records & empty rows. During importing of job is not successfully done, it is throwing error." My question is if the duplicate data lies in 1000th record, let job be success for until 999 record. Then let it can show error for 1000 record & logged .
Please suggest me to how to succeed the job.

Thanks in advance
Karna|||

I'm honestly not certain how I would go about doing that. For most situations where I'm using SSIS, the behavior you're trying to achieve would be inappropriate for my needs (I would either need the entire load to fail with no rows written to the destination, or else have error rows redirected to a log file or table) so I've never tried to set things up this way. I'm also pretty unfamiliar with the Input/Output Wizard, so that's two strikes against me.

With that said, the first thing I would try would be to update the destination component in the package (i do not know if this is exposed through the wizard - i assume it is not) to turn off fast load. This will slow things down quite a bit as each row will be inserted into the destination database one by one, but it might give you what you need.

Perhaps someone else has more experience with this problem than I...

|||Hi Mathew,

Actually I have to run a job for which it has to import csv data file to table. CSV has duplicate records. My job should not import duplicate records, it should append only actual records. If I set primary key & disabled allow null option. After this when I run the job, job is not even appending actual records (which is not duplicated), job is throwing error which is not importing any data from csv file.
Please suggest me to solve this issue.

Thanks in advance
Karna|||Hi Mathew,

Actually I have to run a job for which it has to import csv data file to table. CSV has duplicate records. My job should not import duplicate records, it should append only actual records. If I set primary key & disabled allow null option. After this when I run the job, job is not even appending actual records (which is not duplicated), job is throwing error which is not importing any data from csv file.
Please suggest me to solve this issue.

Thanks in advance
Karna|||

Karna,

I think you best option use the error output in the destination component to redirect the errors. If you don't change the default value of the error configuration; then the package will fail as soon as the 1st duplicate hit the destination table. This is nothing diffrent from what Matthew has already suggested.

|||Hi,

Thanks for reply. My problem is I need to import CSV data file to one of the table in the database. During importing it shouldn't import null rows & same rows to the table. The method which should checks the data in the table before importing, if data exists it shouldn't import any data else it should. Please suggest me to solve this problem.

Thanks in advance
Karna|||

See this link for a number of different ways to see if a row exists:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211340&SiteID=1