Category Archives: SSRS

SQL Server Reporting Services

Log in as a different Windows account to SQL Server Management Studio

There are times when you’ll need to use Windows authentication to get to SQL Server Management Studio, but the Windows login is not the one you’re currently logged into your computer with.  Perhaps you’re a consultant or IT worker and you have logins in several different domains.  Or if you’re working with an application or enterprise system like SharePoint, you may be using several different service accounts to do the configuration, and one or more of those AD accounts may have SQL permissions that your personal login does not.  It’s crazy to be working “blind,” so you need to get into SQL and see those databases or query them sometimes.

You can try to log into a computer using those accounts, but the quick and easy way is to just use a command line run as that user to open SSMS.  If you use this once, then take the extra ten seconds and make yourself a shortcut, because I guarantee it will come up more than once.  Smile  I have three different shortcuts on my desktop, two for my own account on other domains, and one for the SharePoint install service on my own domain.  Note – the below commands are what I use to get to my own SSMS for SQL Server 2008 R2. Obviously check and adjust to the path of your own SQL installation.

Logging into SQL as an AD account in a different domain:

C:\Windows\System32\runas.exe /netonly /user:[domain name]\[username] "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"

 

Logging into SQL as an AD account in your own domain:

C:\Windows\System32\runas.exe /user:[domain name]\[username] "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"

(So in other words, remove the /netonly if you’re using a Windows account in your own domain).

 

In either case, you’ll have a cmd.exe window pop up and you’ll be prompted,

Enter the password for [your domain here]\[the username]:

Simply enter the password for the account you’re using and hit Enter.

 

At that point, if you’ve entered the correct password, SQL Server Management Studio should open.  Select the server you want and be sure to select Windows Authentication.  Now you should be logged into SQL using the AD account that has the SQL permissions you need to do your task.

How to find the Report Data tab in SSRS after closing it

Today I was rocking along in SSRS 2008R2 and then absent mindedly closed the Report Data tab by clicking the x at the top corner!  When I go to the View menu, I do NOT have the choice of Report Data.

no Report Data

Never fear. I’ve found a few ways to get it back. 

One way is to use the key combination Ctrl-Alt-D    But that may not do anything unless you first either double click on the name of the report ([reportname].rdl) in Solution Explorer or simply click on the Design surface where you’re creating the report. 

report in progress

In fact, clicking on the report in progress itself may bring Report Data back to the old View menu immediately!

report data

If all else fails and you can’t seem to get Report Data to reappear, you can always reset your windows to the way Nature intended them.

reset windows

One or more parameters required to run the report has not been specified

I wrote a report in SSRS 2008 R2 last week and published it to a reports library on SharePoint 2010 (SharePoint-integrated Reporting Services).  The report worked well, but then the users decided they didn’t want a couple of the parameters.   I got back into BIDS and removed the two parameters from every place they occurred, then I tested the report.  It worked fine.  It worked fine locally, that is, but once I deployed it to SharePoint I found it would not work on the server.   The error message was, “One or more parameters required to run the report has not been specified.”  The things I did next, I thought I’d list for you, because they might just take care of the problem if this happens to you. 

1. Go to the report in BIDS, right-click,  and select View Code. There, searching for the word parameter, you may see a left-over reference to the parameters you removed.  You can also go to the place you store your SSRS project and inspect the XML for the .rdl and .rsd (dataset) files by opening them in WordPad.

2. Making sure you have the .rdl file safely on your own machine and in your BIDS, go to the SharePoint server and delete the report from the report library before deploying it from BIDS again.  I did that, but it did no good at all.

3. FINALLY I tried deploying the report and its datasources and datasets to a new test site on the SharePoint server and the report worked fine.  So then I went back to my original site and deleted the datasets that went with this report (they were not needed for other reports, just this one).  Then I re-deployed them along with the report and it worked fine on the server. 

 

What was my problem then?  The problem was the one or more of my datasets must have had references to the deleted parameters, although I really didn’t think they’d ever used the parameters.  I did have the properties of that project in BIDS set so that Overwrite Datasets is True, but I guess since all I did was deploy the report itself the datasets weren’t overwritten because I didn’t specify that they be re-deployed.  Moral of the story – redeploy your datasets as well as the report itself if you do some major surgery in SSRS!