An existing connection was forcibly closed by the remote host

It is very appreciated if someone can help me on this problem:

I have 2 machines, connected with workgroup (not domain machine), 1 is win2003 sp1, 1 is xp.

How can I connect SSAS from XP machine with windows authentication?

Looks the Native client fix the windows authentication with local user name.

When I connect from XP, my application with following codes:

string connstr = "Provider=MSOLAP.3;Data Source=amsserver;Password=accenture;User ID=administrator;Initial Catalog=MIP2ASProject";

AdomdConnection objConnection = new AdomdConnection();

// Assign the connection string and try to

//open a connection.

objConnection.ConnectionString = connstr;

objConnection.Open();

It give me this error:

An existing connection was forcibly closed by the remote host

First of all, you dont need the User ID and Password in the connection string if you are connecting using TCP/IP (you would use them only when connecting with HTTP using Basic auth).

Second, in order to use "Provider=MSOLAP.3" you will need to install "Microsoft SQL Server 2005 Analysis Services 9.0 OLE DB Provider" provider from http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi Edward,

Yes, I have installed oledb AS9 provider.

If you think SSAS only support windows authentication which must use inside the domain, I really couldn't understand why oledbconnection works fine with same connection string?

I am not sure about whether impersonation belong to windows authentication, but, my testing prove that oledb can connect in this way, adomd can't.

BTW, thankful if anyone can let me know how to enable SSAS's anonymous connection?

Jerry

|||

it is an antivirus problem

uninstall antivirus and try again

what antivirus do you have?

|||

What version of ADOM.NET are you using?

Try downloading and installing "Microsoft ADOMD.NET " from http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

An error was encountered in the transport layer

I am fairly new to SSAS 2005. I have just created one cube. I was able to create an Excel pivot table that connected to this cube and had all the funtionality that I expected. I then published the pivot table as a web page. When I view the web page on the server where SQL 2005 is loaded the web page comes up with all of the expected functionality. But when I try to view the web page from another computer on my network I gett the error

The query could not be processed:

An error was encounted in the transport layer

The peer prematurely closed the connection.

Any suggestions on how to proceed.

Even though this is a web page, the page actually contains an ActiveX pivot table control which executes on the client machine. The client machines will need to have the OLEDB provider for SSAS 2005 installed in order to connect to SSAS 2005. You can download the "Microsoft SQL Server 2005 Analysis Services 9.0 OLE DB Provider " from the feature pack download page (about half way down) at http://www.microsoft.com/downloads/details.aspx?FamilyID=df0ba5aa-b4bd-4705-aa0a-b477ba72a9cb&DisplayLang=en

If this still does not help, there is a great article on troubleshooting connectivity issues with SSAS at http://sqljunkies.com/WebLog/edwardm/archive/2006/05/26/21447.aspx

|||I've loaded SSAS OLE DB 9.0. I've allowed anonymous users and I still get the error. Any other suggestions.|||Trying using the domain name in front of your user name (domain\username)|||

You could try tracing the SSAS server while you try to connect to see if there are any error events that appear.

The following thread from the newsgroup might also provide some ideas:

http://groups.google.com/group/microsoft.public.sqlserver.olap/browse_frm/thread/fda9edcc5cabaaa6/ae553b59c97dd4b5?lnk=st&q=&rnum=7#ae553b59c97dd4b5

|||

Hi,

I'm having a similar problem except it's less consistent (*sigh*). The situation: I have SSAS 2005 running on a non-domain machine (it's a test setup) along with Project Server 2007. I then have a few client machines I'm trying to connect with. The hitch is that one of them is able to connect with any method I try (.udl file, Excel PivotTable, or the Data Analysis Web components in the Project Server) - in SQL Server Profiler, I see the correct NTUserName appear (I have a couple of users created on the server). However, when I try to connect with another machine using Data Analysis components with IE7, I get the error "An error was encountered in the transport layer." and "The peer prematurely closed the connection."; the profiler shows the NTUserName is "Anonymous", yet I'm using the same user credentials. To make things interesting, on the problem machine, I can successfully connect using the .udl file method and Excel (the NTUserName is correct). Just for completeness, I have no problems when trying to connect locally from the server.

I'm reasonably sure the settings for Internet Explorer are correct and that I have the correct components installed (like OLE DB Provider for Analysis Services 9.0) otherwise no method would work. I'm just not sure why the user credentials are making it okay from one machine and not the other (and from different methods, to boot). Just for funsies, I enabled Anonymous access to SSAS but that had no difference. For reference, the machine that works is Vista and the machine that does not is XP SP2. Both machines are fully patched.

I have tried the suggestions in both of these following Web pages (and a variety of other postings) to no avail:

http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/cisql2005ascs.mspx

and

http://sqljunkies.com/WebLog/edwardm/

Other things:

The firewall on the server is off "Access data sources across domains" is enabled (IE option) "Automatic logon with current user name and password" is enabled in all browsers I try (IE option)

An error was encountered in the transport layer

I am fairly new to SSAS 2005. I have just created one cube. I was able to create an Excel pivot table that connected to this cube and had all the funtionality that I expected. I then published the pivot table as a web page. When I view the web page on the server where SQL 2005 is loaded the web page comes up with all of the expected functionality. But when I try to view the web page from another computer on my network I gett the error

The query could not be processed:

An error was encounted in the transport layer

The peer prematurely closed the connection.

Any suggestions on how to proceed.

Even though this is a web page, the page actually contains an ActiveX pivot table control which executes on the client machine. The client machines will need to have the OLEDB provider for SSAS 2005 installed in order to connect to SSAS 2005. You can download the "Microsoft SQL Server 2005 Analysis Services 9.0 OLE DB Provider " from the feature pack download page (about half way down) at http://www.microsoft.com/downloads/details.aspx?FamilyID=df0ba5aa-b4bd-4705-aa0a-b477ba72a9cb&DisplayLang=en

If this still does not help, there is a great article on troubleshooting connectivity issues with SSAS at http://sqljunkies.com/WebLog/edwardm/archive/2006/05/26/21447.aspx

|||I've loaded SSAS OLE DB 9.0. I've allowed anonymous users and I still get the error. Any other suggestions.|||Trying using the domain name in front of your user name (domain\username)|||

You could try tracing the SSAS server while you try to connect to see if there are any error events that appear.

The following thread from the newsgroup might also provide some ideas:

http://groups.google.com/group/microsoft.public.sqlserver.olap/browse_frm/thread/fda9edcc5cabaaa6/ae553b59c97dd4b5?lnk=st&q=&rnum=7#ae553b59c97dd4b5

|||

Hi,

I'm having a similar problem except it's less consistent (*sigh*). The situation: I have SSAS 2005 running on a non-domain machine (it's a test setup) along with Project Server 2007. I then have a few client machines I'm trying to connect with. The hitch is that one of them is able to connect with any method I try (.udl file, Excel PivotTable, or the Data Analysis Web components in the Project Server) - in SQL Server Profiler, I see the correct NTUserName appear (I have a couple of users created on the server). However, when I try to connect with another machine using Data Analysis components with IE7, I get the error "An error was encountered in the transport layer." and "The peer prematurely closed the connection."; the profiler shows the NTUserName is "Anonymous", yet I'm using the same user credentials. To make things interesting, on the problem machine, I can successfully connect using the .udl file method and Excel (the NTUserName is correct). Just for completeness, I have no problems when trying to connect locally from the server.

I'm reasonably sure the settings for Internet Explorer are correct and that I have the correct components installed (like OLE DB Provider for Analysis Services 9.0) otherwise no method would work. I'm just not sure why the user credentials are making it okay from one machine and not the other (and from different methods, to boot). Just for funsies, I enabled Anonymous access to SSAS but that had no difference. For reference, the machine that works is Vista and the machine that does not is XP SP2. Both machines are fully patched.

I have tried the suggestions in both of these following Web pages (and a variety of other postings) to no avail:

http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/cisql2005ascs.mspx

and

http://sqljunkies.com/WebLog/edwardm/

Other things:

The firewall on the server is off

"Access data sources across domains" is enabled (IE option)

"Automatic logon with current user name and password" is enabled in all browsers I try (IE option)

Am I physically connected to the server on the computer I am using

Hi All:

Is there a way to tell if I am connected to the server on the computer I am using to connect with?

I want to determine whether the connection is to the local server or a remote server.

Thanks.

Doug.

Doug,

The name of the server should appear in your 'Object Explorer' window. This will tell you whether you are connected to your local instance or a remote server.

Good Luck...

'Avarus animus nullo satiatur lucro - A greedy mind is satisfied with no (amount of) gain

|||

My fault, should have been more specific.

I am connecting to the server using ADO; is there any command, TSQL, etc that will let me know if the connection is to the local server?

|||

Execute the query (CommandText):

SELECT @.@.SERVERNAME

The return value will be the name of the Server you are connect with when the query is executed.

|||

Hi Arnie:

Perhaps I'm missing the point here... but would not that only give me the name of the local server, not the fact that I am actually sitting at the same workstation?

I did find another TSQL function called HOST_NAME() -- but I believe that returns the workstation name where the server resides, not the workstation I am sitting at?

So, I can get the host name and servername, but what I need is to know if there is a way to determine the I am physically at the workstation that hosts the server?

|||

I did some more testing.

I connected to a remote machine and the host_name() returned the workstation I was physically at, and the @.@.servername returned the name of the local server on the host machine (if there is no local server then it would return null, right?).

So it looks to me that those functions alone will not work, as I need to know if I was connected remotely (which was the case) and they did not seem to help.

Unless... what if I compared the DataSource of the connection string to the servername?

If the hostname = servername, and the datasource = servername, then I am connected locally?

Actually, now that I think about it... I don't even need hostname, but the servername.

If the datasource = servername, then I am connected locally, right?

|||

You are on the right track.

HOST_NAME() returns the client computer name.

@.@.SERVERNAME returns the SQL Server computer name/instance.

If the computer names don't match, the computers are different.

HOWEVER, the HOST_NAME() could be [MyComputer] and the @.@.SERVERNAME could be [MyComputer\SQLExpress] -a Named INSTANCE of SQL Server running on the computer [MyComputer]. So you would need to check for a backslash [\], and if present, use the portion of the @.@.SERVERNAME to the left of the backslash to compare with HOST_NAME().

DataSource = @.@.SERVERNAME 'should' always match. DataSource is the part of your connection string that directs the connection to a Server.

|||

Got it, thanks!

|||

Doug,

If you have Alerts on, then you will get this message.

I was half asleep. You can also use SERVERPROPERTY('MachineName') on the SQL Server. It will return the computer name without the Instance name -so you will NOT have to parse @.@.ServerName.

Just check

Code Snippet

IF host_name() <> serverproperty('machinename')
BEGIN
PRINT 'Remote Connection'
END
ELSE
BEGIN
PRINT 'Local Connection'
END

Am I physically connected to the server on the computer I am using

Hi All:

Is there a way to tell if I am connected to the server on the computer I am using to connect with?

I want to determine whether the connection is to the local server or a remote server.

Thanks.

Doug.

Doug,

The name of the server should appear in your 'Object Explorer' window. This will tell you whether you are connected to your local instance or a remote server.

Good Luck...

'Avarus animus nullo satiatur lucro - A greedy mind is satisfied with no (amount of) gain

|||

My fault, should have been more specific.

I am connecting to the server using ADO; is there any command, TSQL, etc that will let me know if the connection is to the local server?

|||

Execute the query (CommandText):

SELECT @.@.SERVERNAME

The return value will be the name of the Server you are connect with when the query is executed.

|||

Hi Arnie:

Perhaps I'm missing the point here... but would not that only give me the name of the local server, not the fact that I am actually sitting at the same workstation?

I did find another TSQL function called HOST_NAME() -- but I believe that returns the workstation name where the server resides, not the workstation I am sitting at?

So, I can get the host name and servername, but what I need is to know if there is a way to determine the I am physically at the workstation that hosts the server?

|||

I did some more testing.

I connected to a remote machine and the host_name() returned the workstation I was physically at, and the @.@.servername returned the name of the local server on the host machine (if there is no local server then it would return null, right?).

So it looks to me that those functions alone will not work, as I need to know if I was connected remotely (which was the case) and they did not seem to help.

Unless... what if I compared the DataSource of the connection string to the servername?

If the hostname = servername, and the datasource = servername, then I am connected locally?

Actually, now that I think about it... I don't even need hostname, but the servername.

If the datasource = servername, then I am connected locally, right?

|||

You are on the right track.

HOST_NAME() returns the client computer name.

@.@.SERVERNAME returns the SQL Server computer name/instance.

If the computer names don't match, the computers are different.

HOWEVER, the HOST_NAME() could be [MyComputer] and the @.@.SERVERNAME could be [MyComputer\SQLExpress] -a Named INSTANCE of SQL Server running on the computer [MyComputer]. So you would need to check for a backslash [\], and if present, use the portion of the @.@.SERVERNAME to the left of the backslash to compare with HOST_NAME().

DataSource = @.@.SERVERNAME 'should' always match. DataSource is the part of your connection string that directs the connection to a Server.

|||

Got it, thanks!

|||

Doug,

If you have Alerts on, then you will get this message.

I was half asleep. You can also use SERVERPROPERTY('MachineName') on the SQL Server. It will return the computer name without the Instance name -so you will NOT have to parse @.@.ServerName.

Just check

Code Snippet

IF host_name() <> serverproperty('machinename')
BEGIN
PRINT 'Remote Connection'
END
ELSE
BEGIN
PRINT 'Local Connection'
END

Am I a sysadmin member?

Hi,

I want to check if the currently connected user is a sysadmin(or securityadmin) member. Could I only check whether the user has the CONTROL SERVER permission or not? Is there a straight way to test it?

Try using

SELECT IS_SRVROLEMEMBER( 'sysadmin', 'UserName' )

Returns 1 for true, 0 for false.

|||Thanks