Develop CRM SSRS Report in Local using “Windows Authentication” without really using the Joined Domain Windows Account

Hi all,

Just want to share how to develop SSRS Report that connect to the CRM Database, but using Windows Authentication.

Background

Sometimes we need to develop custom report because report wizard cannot support the complex report and complex UI or filter or grouping, so that is the reason why we still need to rely on this SSRS.

As we know that using FilteredView is the recommended way query CRM Database that using Security Layer applied on the query, but this FilteredView we can only run if we use the valid CRM User, which is not other than using Windows AD Account registered in AD and CRM. Then, we found problem that mostly developers are using their own local Visual Studio, of course we cannot use the server to install VS and let developers to use that imagine we have more than 10 developers trying to access and remote desktop to server? So troublesome.

And here are the list that what developers usually do to develop report:

1. Access the Server (remote Desktop), installing VS and develop there, using Windows Authentication, so that you can do query using FilteredView as usual

It is a good idea to settle everything, but will make burden on the server and cannot imagine if many developers want to develop and access at the same time, who will kick and be kicked everytime.

2. Other way is we use SQL Authentication using “sa” account to access, but this can be troublesome because as mentioned before, FilteredView, you can only access using CRM AD User. What you need for this is just access to the SQL Server Database.

3. Then, try another way is you restore the database locally to your SQL Server Express probably, then run the query locally, but, again, you cannot use FilteredView in your Query. This also needs you to do regular backup if there is changes in the database schema

For point number 2 and 3, you can do that and can run the report so long you have access to the Database, but the bad thing is you cannot run FilteredView and you must using standard View then before you deploy to CRM you must manually adding the “filtered” keyword before your View name and you cannot run the report view the data act on behalf of a CRM User based on the security because you are not CRM user running that time.

Solution

As we know that solution number 1 is good for faster development without manually changing, but might result other weakness in other areas, so we need to combine those 3 items.

Now this is what you need to do:

1. Run the cmd (CTRL + R) then type “cmd”
2. And run this command:

runas /netonly /user:yourdomainname\yourusername "C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\devenv.exe"

3. Example:



Then it will prompt you to fill in password:

Result

I found this solution is quite useful because we can develop and run preview the report locally without remote desktop-ing to server and can use the recommend FilteredView query.
Then here is the result:

*Before:

Keep loading database names



And in the end prompting this error message or when you click the “Test Connection” as well:



*After

Now we can successfully login and can retrieve the database



Hope this helps!