Data Warehouse SQL Server Connection Strings

The Data Warehouse has the following connection strings to the SQL Server database:

Server-Side Connection String: connstr_dw

Server-Side Connection String: connstr_db_dw

Client-Side Connection String: connstr_db_Analysis

Server-Side Connection String: connstr_dw

This is the Data Warehouse connection string for the OLE DB Provider for Commerce Server.

If you are using SQL Authentication, the format of this connection string is:

"URL=mscop://InProcConnect/Server=myserver:Database=dbname: 
Catalog=DWSchema:Trusted_Connection=Yes:FastLoad=True:Connection_Retry=10:Connection_Retry_Delay=30" 

Or,

"Provider=commerce.dso.1;Data 
Source=mscop://InProcConnect/Server=myserver: 
Catalog=DWSchema:Database=dbname:Trusted_Connection=Yes:FastLoad=True:Connection_Retry=10:Connection_Retry_Delay=30" 

If you are using Windows Authentication, the format of this connection string is:

"URL=mscop://InProcConnect/Server=myserver:Database=dbname:Catalog=DWSchema:Trusted_Connection=true:FastLoad=True:Connection_Retry=10:Connection_Retry_Delay=30" 

Or,

"URL=mscop://InProcConnect/Server=myserver:Database=dbname:Catalog=DWSchema:Trusted_Connection=Yes:FastLoad=True:Connection_Retry=10:Connection_Retry_Delay=30" 

Server-Side Connection String: connstr_db_dw

In the ASP code this is g_MSCSDataWarehouseSQLConnStr.

This is the server-side Data Warehouse connection string used by the Analysis modules to retrieve data to populate the ListSheets in the Reports and Completed Reports modules. This connection string is used in the following tasks:

• During the execution or export of any report.
• To delete report definitions and completed reports.
• To save dynamic reports.

This connection string needs permissions to create and delete tables in the Data Warehouse as well as permissions to select, insert, update, and delete records from Data Warehouse tables and to execute stored procedures. You set this connection string when you unpack the Data Warehouse resource; it has Administrative privileges.

If you are using SQL Authentication, the format of this connection string is:

Provider=SQLOLEDB.1;Data Source=ServerName;Initial 
Catalog=DataWarehouseName;Integrated Security='SSPI';Extended 
Properties=;Network Library=dbmssocn

If you are using Windows Authentication, the format of this connection string is:

'Provider=SQLOLEDB.1;Data Source= ServerName;Initial Catalog=
 DataWarehouseName;Integrated Security=SSPI;Persist Security 
Info=False;Network Library=dbmssocn'

Client-Side Connection String: connstr_db_Analysis

In the ASP code this is g_MSCSDataWarehouseSQLConnStrPivot.

This is the client-side Data Warehouse connection string used by the PivotTable in the dynamic reports to query for the report data. Running SQL-based dynamic reports requires that the logged-on Windows user has Select permissions to all database objects referenced in the report query.

Clients can access this connection string by viewing the source when a dynamic report renders. Consequently, this connection string uses Windows Authentication by default, even if you specify SQL Security when you unpack the Data Warehouse resource.

The format of this connection string is:

'Provider=SQLOLEDB.1;Data Source= ServerName;Initial Catalog= 
DataWarehouseName;Integrated Security=SSPI;Persist Security 
Info=False;Network Library=dbmssocn'

Because this connection string uses Windows Authentication, Windows users that do not have SQL Server permissions will not be able to run dynamic SQL reports.

To allow many different users to run dynamic SQL reports, create a Windows user group with SQL Server permissions. For more information, see Scripts for Securing Databases Accessed by Reports.

Note

• Commerce Server 2000 does not include this connection string. When you upgrade from Commerce Server 2000 to Commerce Server 2002, the connection string information for this resource is copied from connstr_db_dw. You must then manually change connstr_db_Analysis to use Windows Authentication.

See Also

Configuring Data Warehouse Connection Strings

OLE DB Connection Strings for the Profiling System

Copyright © 2005 Microsoft Corporation.
All rights reserved.