Continued Analysis Services Remote connect

hello,

i am trying to connect to Analysis services server through another pc but i am unable to do so.

Using the following commands i manage to connect to analysis services server at my pc

Server.srv=new Server ();

srv.Connect('ip address or name');

but not to another pc. Are there any settings under sql server should i change ?

I assume that you are using Analysis Services 2005.

First, make sure that the Analysis Services allows remote connections:

On the machine running analysis services, please perform the following steps:

- All programs\Microsoft SQL Server 2005\Configuration Tools\Sql Server Surface Area Configuration

- Click on the Surface Area Configuration for Services and Connections

- Select Remote Connections under the MSSQLSERVER\Analysis Services node

- Make sure that Local and Remote connections is selected (to allow remote connections)

At this point, the client machine should be able to connect to the remote server. Before everything works, you will need to make sure that the client machine connection code is running under credentials that can access Analysis Services

|||

Well i have already enable remote connections and chech credentials as well

This is the error i am getting :

{"A connection cannot be made. Ensure that the server is running."} System.Exception {Microsoft.AnalysisServices.ConnectionException}

{"A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond"} System.Exception {System.Net.Sockets.SocketException}

Any help ? thanks.

|||That is unusual. Does PING work for the remote computer? Also, could you please check any firewall settings on the remote computer? Analysis Services (msmdsrv.exe) needs permissions through the firewall|||

PING works fine among the two ps's and analysis services is excepted under firewall .

I am still facing the same problem.

Can you please provide me the exact connection string from a pc1 with ip 10.181.11.1 to a pc2 with ip 10.181.11.2 (asp form in pc1 to analysis server in pc2)?

Maybe i use a wrong connection string

This is the error i am getting :

Unable to read data from the transport connection : an existing connection was forcibly closed by the remote host

Any help?

Thanks

|||

Are you using OLE DB or Adomd.Net?

The Connection String should look like "Data Source=10.181.11.2; Initial Catalog=<Your Database>"

One possible reason for the error is that your current user does not have permissions on the server. Are the computers in the same domain? If not, is Everyone allowed to access a database on the server?

|||

I am using ADOMD connection,,if you have an example connection string it would be helpful to check with the one i use

(i have tried different ways of setting the connection string but still cannot connect ).

As far as user credentials are concerned i have also tried to set them in different ways.

I am not quite sure which user (WinXP user, Database Engine User , IIS user, Analysis Services user) must have access to which place,, i am a bit confused about this.

I appreciate your help. Thank you

|||

To begin, let's make sure the physical connection works correctly. On the Analysis Services machine, create a new Analysis Services role which has permissions on your target database, and make sure that Everyone is a member of that role.

Then, try to connect from your client machine, using a connection string like

"Data Source=<IP of the remote machine>; Initial catalog=<name of the DB which is accessible to Everyone>"

If this works, the physical connection is OK and we'll focus on credentials.

Are your machines part of the same Windows NT domain? Tell me a few things about your application, I understand it is a web application, is it correct? Is it impersonating the remote user or running uner a set of specified credentials, or running under the default credentials (machine\ASPNET user)?

And, if you don't mind, could you please reply to this thread rather than creating a new post? It is easier to keep track of the issues

|||

We did what you suggested but we still get the same error.

Do we need to give the permissions and to the actual database (database engine) except to the one in analysis services ?

Our machines are part of the same workgroup connected via a router.

It is a web application and it is runnig under the default credentials.

But we tried with a windows application to check if we can connect to the analysis services and we still get the error

We can access a remote database engine througn sql server management studio but not remote analysis services server.

Is there a way to access a remote analysis services through sql server management studio?

Thanks

|||

This reply involves another error (not the one mentioned above). It seems to have a problem in creating the DIMENSION through source code and the problem seems to be on 'Process' command. We removed the Process command from the code and tried to process it through anaysis services management studio and still got error 'process failed' . This is a critical issue ,,i cannot continue to create cubes, etc.

As far as my application is concerned ,,it is a web application ,,the user will choose through an asp form measures and dimensions and then dimensions,cubes,structures will be created dynamically. So the problem is at initial state since i cannot create the dimenions.

Pls help.

This is the source code i use for dimension creation :

static void CreateDateDimension(Database db)

{

// Create the Date dimension

Dimension dim = db.Dimensions.Add("Invitquest");

dim.Type = DimensionType.Time;

dim.UnknownMember = UnknownMemberBehavior.Hidden;

dim.AttributeAllMemberName = "All Periods";

dim.Source = new DataSourceViewBinding(datasourceName);

dim.StorageMode = DimensionStorageMode.Molap;

#region Create attributes

DimensionAttribute attr;

attr = dim.Attributes.Add("Invitquest");

attr.Usage = AttributeUsage.Key;

attr.Type = AttributeType.Date;

attr.OrderBy = OrderBy.Key;

attr.KeyColumns.Add(CreateDataItem(db.DataSourceViews[0], "Invitquest", "invno"));

// attr.NameColumn = CreateDataItem(db.DataSourceViews[0], "Invitquest", "tableid");

attr = dim.Attributes.Add("YPopto");

attr.Type = AttributeType.Regular;

attr.KeyColumns.Add(CreateDataItem(db.DataSourceViews[0], "Invitquest", "Ypopto"));

// attr.NameColumn = CreateDataItem(db.DataSourceViews[0], "Invitquest", "Ypopto");

attr = dim.Attributes.Add("cancer");

attr.Type = AttributeType.Regular;

attr.KeyColumns.Add(CreateDataItem(db.DataSourceViews[0], "Invitquest", "CANCER"));

// attr.NameColumn = CreateDataItem(db.DataSourceViews[0], "Invitquest", "CANCER");

attr = dim.Attributes.Add("AM BIO");

attr.Type = AttributeType.Regular;

attr.KeyColumns.Add(CreateDataItem(db.DataSourceViews[0], "Invitquest", "AMBIO"));

// attr.NameColumn = CreateDataItem(db.DataSourceViews[0], "Invitquest", "AMBIO");

#endregion

dim.Update();

}

Thanks.

|||I merged all these threads.|||

> Our machines are part of the same workgroup connected via a router.

This seems to be the issue. Analysis Services uses exclusively Windows Integrated security. User accounts are not shared across machines in a workgroup, only in a domain (or related domains). This might explain both the management studio failure as well as the custom application failure.

Can you try allowing Everyone access to the Analysis Services database? Eventually , the permissions for the Everyone role can be restricted to what is needed (most likely, Read Definition and predictions), but you can start with Everyone as administrator -- not a great security suggestion, but it allows you to easily figure out if this is the source of the problem.

Constraint and Linked Server query problem

Hi,

I've got a problem querying a remote table via linked server (SQL Server 2000 SP4).
If I do
select * from <linked server>.<database name>.<db owner>.<Table1>
it returns all data, as expected.
However, if I do:
select * from <linked server>.<database name>.<db owner>.<table name>
where ColumnID = 51588
it doesn't return a row, even though it exists in the remote table.

A likely reason for this could be the fact that the column ColumnID has a constraint on it:
ALTER TABLE [dbo].[Table1] ADD
CONSTRAINT [repl_identity_range_sub]
CHECK NOT FOR REPLICATION ([ColumnID] > 90000 and [ColumnID] < 95000)

I've tried making the constraint NOCHECK, but still get the same problem.
ALTER TABLE [dbo].[REGION_1] NOCHECK CONSTRAINT [repl_identity_range_sub]

Does anyone have an idea why this is hapenning and how to get around it (other than obviously just dropping the constraint - which works by the way, but can not be done as it is used).

Any help would be greatly appreciated.

NR

Hi,

never heard of that issue. If you have control over the remote server, start a SQl profiler session to see which statement is arriving on which database. Perhaps you are connection to the wrong server (or to another database). Querying with filters on remote server is no magic, so that should be no problem.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Hi,

Already tried that and it's no help, all I can see is some system sps executing: sp_tables_info_rowset, sp_columns_rowset, sp_indexes_rowset, sp_check_constbytable_rowset, sp_table_statistics_rowset, a few DBCC SHOW_STATISTICS and then it executes sp_getschemalock followed by exec sp_releaseschemalock 1.

All those are for my table.

Weird thing is that a select * from the table works. Filtering on any other columns other than the one with constraint on it also works, it is only using the column with the constraint that cosistently does not work.

NR

|||

I would make sure that you are turning off constraints and doing the select in the same transaction. Otherwise I don't have any ideas.

It doesn't address the issue, but you may just want to do a pass-through openquery select to get around the issue.

|||

I'm afraid it doesn't help as the constraint is already marked as NOCHECK, and I'd rather not have to drop and recreate it (as it's put on by merge replication).

NR

|||I know it isn't a real answer to the problem, but if your needs are limited, you may want to just create a view that is outside the replication scheme and pull the remote data using the view. Good luck.|||

Hi,

I have the same problem and found the following solution to the problem.

I added an additional check constraint to each questionable table to deactivate the "optimization" filter that was intended for effective table partitioning:

ALTER TABLE [dbo].[Customer] WITH NOCHECK ADD CONSTRAINT [CK_Customer_AntiPartitioning] CHECK NOT FOR REPLICATION ([CustomerID] > 0)
ALTER TABLE [dbo].[Customer] NOCHECK CONSTRAINT [CK_Customer_AntiPartitioning]

the trick is that the optimization feature needs an unambiguous check constraint set that is not the case, because the replication range always overlap with the above added range. the "nocheck constraint" statement disables actual checking so that you do not have significant performance penalty

Greetings - Richie

Constraint and Linked Server query problem

Hi,

I've got a problem querying a remote table via linked server (SQL Server 2000 SP4).
If I do
select * from <linked server>.<database name>.<db owner>.<Table1>
it returns all data, as expected.
However, if I do:
select * from <linked server>.<database name>.<db owner>.<table name>
where ColumnID = 51588
it doesn't return a row, even though it exists in the remote table.

A likely reason for this could be the fact that the column ColumnID has a constraint on it:
ALTER TABLE [dbo].[Table1] ADD
CONSTRAINT [repl_identity_range_sub]
CHECK NOT FOR REPLICATION ([ColumnID] > 90000 and [ColumnID] < 95000)

I've tried making the constraint NOCHECK, but still get the same problem.
ALTER TABLE [dbo].[REGION_1] NOCHECK CONSTRAINT [repl_identity_range_sub]

Does anyone have an idea why this is hapenning and how to get around it (other than obviously just dropping the constraint - which works by the way, but can not be done as it is used).

Any help would be greatly appreciated.

NR

Hi,

never heard of that issue. If you have control over the remote server, start a SQl profiler session to see which statement is arriving on which database. Perhaps you are connection to the wrong server (or to another database). Querying with filters on remote server is no magic, so that should be no problem.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Hi,

Already tried that and it's no help, all I can see is some system sps executing: sp_tables_info_rowset, sp_columns_rowset, sp_indexes_rowset, sp_check_constbytable_rowset, sp_table_statistics_rowset, a few DBCC SHOW_STATISTICS and then it executes sp_getschemalock followed by exec sp_releaseschemalock 1.

All those are for my table.

Weird thing is that a select * from the table works. Filtering on any other columns other than the one with constraint on it also works, it is only using the column with the constraint that cosistently does not work.

NR

|||

I would make sure that you are turning off constraints and doing the select in the same transaction. Otherwise I don't have any ideas.

It doesn't address the issue, but you may just want to do a pass-through openquery select to get around the issue.

|||

I'm afraid it doesn't help as the constraint is already marked as NOCHECK, and I'd rather not have to drop and recreate it (as it's put on by merge replication).

NR

|||I know it isn't a real answer to the problem, but if your needs are limited, you may want to just create a view that is outside the replication scheme and pull the remote data using the view. Good luck.|||

Hi,

I have the same problem and found the following solution to the problem.

I added an additional check constraint to each questionable table to deactivate the "optimization" filter that was intended for effective table partitioning:

ALTER TABLE [dbo].[Customer] WITH NOCHECK ADD CONSTRAINT [CK_Customer_AntiPartitioning] CHECK NOT FOR REPLICATION ([CustomerID] > 0)
ALTER TABLE [dbo].[Customer] NOCHECK CONSTRAINT [CK_Customer_AntiPartitioning]

the trick is that the optimization feature needs an unambiguous check constraint set that is not the case, because the replication range always overlap with the above added range. the "nocheck constraint" statement disables actual checking so that you do not have significant performance penalty

Greetings - Richie