Connection Strings

Connection strings vary wildly by the type of processing extensions used in the data set. For example, if you use the OLEDB or ODBC process, you must specify the driver. For SQL Server, you should specify a database name, whereas for Oracle the database name is not required. For XML, just point it to the source by entering a URL in the connection string. In all cases, you should not specify the credentials used in accessing the data source inside the connection string. SSRS stores data source credentials separately.

Note

If you are developing with a fixed user ID and password for your data source credentials and you happen to use an embedded data source, the user ID and password will not carry over to the Report Server. This is because your machine and the Report Server will undoubtedly have different encryption keys.

The following are some common connection strings:

• SQL Server 2000 and above

   Data source=MyServer\MyInstance,1433;Initial Catalog=Pubs 

• Analysis Services (SQL 2005)

   Data Source= MyServer\MyInstance,1433;initial catalog=AdventureWorksDW 

• Analysis Services

   provider=MSOLAP.2;data source=MyOLAP;initial catalog=AdventureWorksDW 

• Oracle

   Data Source=OracleSID 

• XML via URL

   URL="http://MyWebServer.com/Queryresults.aspx" 

• XML via Web Service

  [View full width]

   
   [View full width] 
   URL=<url>; SOAPAction=<method-uri>[#/]<method-name>         URL=http://MyReportServer/reportserver/reportservice.asmx;         SOAPAction="http://schemas.microsoft.com/sqlserver/2004/05/reporting  /reportservices/ListChildren" 

Note

When specifying XML as a data source, the credentials should be set to integrated security or "No credentials" for anonymous access. Anything else is ignored.

• SSIS Package

   -f c:\packagename.dtsx 

• SAP Connector

  [View full width]

   
   [View full width] 
   ashost=SAPAppServer client=000 snc_mode=1 sysnr=00 type=3 user=SAPDOTNET  snc_partnername=\"p:[e-mail]\"; 

Note

SAP Connector allows SSRS to connect and communicate with SAP Business Objects. SAP Connector is a separate download. Additional information can be found at http://www.microsoft-sap.com/overview_sap_connector.html and http://msdn2.microsoft.com/en-us/library/ms345256.aspx.