Reporting project Shared data source is losing its password

I have a Reporting Services project in BIDS 2008 R2 (or VS 2008 if you like). The project contains 4 different shared data sources. Those shared data sources were connecting to different database servers with database accounts and passwords. The project is under TFS source control. The project has been deployed to 2 different servers in a debug and release environment. Recently I received a new development system with a fresh installation of SQL 2008 R2 developer. With BIDS I connected to the TFS server and downloaded the most recent version of my reporting project. All project files had been checked in beforehand on the old development system. I had to check out and modify a report. When I wanted to preview the report in BIDS I noticed the error message that it could not connect to one of the four shared data sources. For deployment my project is setup to NOT overwrite shared data sources.

I presumed that the data source password was lost because it was not stored on this new system. So I checked out the data source opened it and edited the connection in the Shared Data Source properties. Indeed the password and login name where blank. I entered them, tested the connection and closed the properties dialog box by clicking OK. For the other 3 data sources I had to do the same: check out, edit property, reenter credentials and save.

However when I previewed the report I received the same error message, the data source could not connect. I went back to the data source properties and tested the connection: everything was OK. But the report preview still did not work. I saved everything in the project and closed the solution. Then reopened it, went to the shared data source properties and indeed the credentials were blank again.

I checked the file system, all 4 files where write enabled, but only 3 files had a modification time of my recent changes, while the data source with the blank credentials had the modification time of when I downloaded the file from TFS.

I stopped BIDS and deleted the rptproj.user file, restarted BIDS, but that did not help.

I tried running BIDS as administrator and save the credentials for that particular data source but that did not help either.

I checked out the complete solution, entered the credentials for the data source again, but that did not help either.

Since I had everything checked out, I decided to delete the data source and then create it again with the same name. This solution worked. The credentials where now stored each time I reopened the project, I could see the credentials stored in the data source properties and preview the report.

Use Windows account to grant SSIS packages R/W access to files on file share

Problem

To schedule a job that runs SSIS packages that need read/write access on folders and files on a file share, the windows account running the SSIS service needs access to the file share, or you can setup a proxy account with a dedicated windows account that has access to the file share and that can run the SSIS package. Packages are stored in the SSIS package store.

A solution is also explained in Running a SSIS Package from SQL Server Agent Using a Proxy Account

Solution

• Create a windows account that has sufficient rights on the file share.
  • e.g. MYDOMAIN\ssisfileuser with password xyz
• On the SQL server where the SQL Server Agent will be running the jobs, add the windows account as a new login.
  • In the user mapping of the Login properties, select the ‘msdb’ database and assign the ‘db_ssisoperator’ Database role membership for msdb to the login.
• To run the package from a SQL job, the SQL Agent has to use the windows account as a proxy, to define the proxy we have to define a credential, where the password of the windows user can be stored.
  • Under Security / Credentials add a new credential
    • Credential name: ssisfileusercred
    • Identity: MYDOMAIN\ssisfileuser
    • Password: xyz
  • Under SQL Server Agent / Proxies, add a new proxy
    • Proxy name: ssisfileuserproxy
    • Credential name: ssisfileusercred
    • Activate the following subsystems:
      • Operating system (cmdexec) (if you are still running on 64-bit version of SQL 2008, not on SQL 2008 R2)
      • SQL server Integration Services Package
• Define a new job to be run under the windows account
  • Under SQL Server Agent Jobs, add a new job
    • General
      • owner can be your own windows account name
      • category : I use ‘Data collector’ to put the job under this kind of category
    • Steps – add new step
      • Name: use a name of your choice, I use the same name as the package to run in this step.
      • Type: Sql Server Integration Services Package
      • Run as:ssisfileuserproxy
      • General
        • Package source: SSIS Package store
        • Server: my servername
        • Use windows Authentication is default and only setting
        • Package: select the package name to be run.
    • Schedules: add a schedule when to run the job.

With this approach you can schedule packages that need file access on a share to run from the SSIS package store.

BI Development Studio 2008 integrate with TFS 2010

To let a SQL developer create BI projects for SQL 2008 and save the source in TFS 2010.

Installation

• Download and install Team Explorer 2008 (This is an ISO file, if you don’t have an ISO image reader try installing Virtual clonedrive or a similar utility)
• from the ISO image run setup.exe
  
• Download and install Visual Studio 2008 SP1 (This small installer will download the full installa and upgrade both VS2008 and Team Explorer 2008 to SP1, if the machine on which BI development Studio is installed, has no full internet access and rights, you may have to look for the full package download)
• run the executable VS90…..exe
• 
• Download and install VSTS 2008 Forward Compatibility Update
• 

Connecto to TFS

• Start BI Developmnet Studio
• In the menu View, select Other windows / Source control Explorer
• 
• In the menu Tools, select Connect to Team Foundation Server…
• 
• In the Connect window, Click on Servers…
• 
• In the Add/Remove window click on Add…
• 
• or if the TFS server is not the default root and you have to specify a site name, e.g. ‘Tfs’ then type the full url to the TFS site, ‘http://myserver:8080/tfs’
• 
• this will connect to the default TFS collection
• If you need access to another collection, you need to add a new server connection and type the full name of the collection. e.g. if the collection’s name is ‘Accounting projects’ (with spaces) then the full url will to connect is ‘http://myserver:8080/tfs/Accounting%20projects’