Wednesday, 23 February 2011

How to Compact your Virtual PC Hardisk Files (VHD)

  1. Download and install Virtual PC Service Pack 1.
  2. Defragment your guest operating system.
  3. Install Guest Additions if its not already
  4. From the CD menu, select Capture ISO Image and browse to the Virtual PC additions directory, which is %ProgramFiles%\Microsoft Virtual PC\Virtual Machine Additions by default.
  5. Mount Virtual Disk Precompactor.iso. By default with auto-run enabled, the precompactor will start automatically.
  6. When it has finished, unmount the ISO from the CD menu.
  7. Run the Virtual Disk Wizard from the File menu and follow the instructions to compact your VHD file.

Saturday, 19 February 2011

ReportViewer - Sub Report with integrated security problem

I have recently had a problem with using Sub Reports in Report Viewer when the SQL server is a seperate box from the web server. The initial data is loaded correctly but when the subreport processing event is fired the second SQL command comes back with a failed login error as it was trying to connect with the local system account of the web server. The connection strings to the database are all using Integrated Security so the impersonation context needs to be there to access the database as the correct user.

After much investigation is looks as though this is caused by a phenomenon called a 'double hop'. The request is sent from the web server, to the SQL server and identity information is passed across to the SQL server. When the sub report is processing the request is again made to the SQL server but at this point the indentity information is lost as it only lasts 2 'hops' This is aparently by design for NTLM authentiation. I have read this can be fixed by using Kerberos authentication but I know nothing about this.

Two solutions that I have come up with seem to work.

1) Change all connection strings to the database to explicity specify the UserID and Password for the SQL connection. This seems to work ok but unless you want to mess about encrypting web configs and connect strings then this isnt really an option.

2) Change the user account the web applications app pool is running under. I changed this account to use the same domain user account that the web application was impersonating. It seems the sub report request is using the identity of the application pool to connect. Changing this then uses the correct user account for the integrated security in the connection string. One thing to note with this method is that I had to change the Anonymous access user in IIS to also use this domain account as a login box was being presented when browsing to the site.

Here is the code for the aspx page that is loading the report. The problem manifests itself in the LocalReport_SubreportProcessing event.

Friday, 18 February 2011

How to open SQL server ports on the firewall

Start Notepad.
Copy and paste the following code into Notepad:

@echo ========= SQL Server Ports ===================
@echo Enabling SQLServer default instance port 1433
netsh firewall set portopening TCP 1433 "SQLServer"
@echo Enabling Dedicated Admin Connection port 1434
netsh firewall set portopening TCP 1434 "SQL Admin Connection"
@echo Enabling conventional SQL Server Service Broker port 4022
netsh firewall set portopening TCP 4022 "SQL Service Broker"
@echo Enabling Transact-SQL Debugger/RPC port 135
netsh firewall set portopening TCP 135 "SQL Debugger/RPC"
@echo ========= Analysis Services Ports ==============
@echo Enabling SSAS Default Instance port 2383
netsh firewall set portopening TCP 2383 "Analysis Services"
@echo Enabling SQL Server Browser Service port 2382
netsh firewall set portopening TCP 2382 "SQL Browser"
@echo ========= Misc Applications ==============
@echo Enabling HTTP port 80
netsh firewall set portopening TCP 80 "HTTP"
@echo Enabling SSL port 443
netsh firewall set portopening TCP 443 "SSL"
@echo Enabling port for SQL Server Browser Service's 'Browse' Button
netsh firewall set portopening UDP 1434 "SQL Browser"
@echo Allowing multicast broadcast response on UDP (Browser Service Enumerations OK)
netsh firewall set multicastbroadcastresponse ENABLE

Save the file as a .txt file by using the following name: OpenSqlServerPort.txt
Rename the OpenSqlServerPort.txt file to the following: OpenSqlServerPort.bat

run the batch file on the server you would like to open the ports and you should be good to go

Wednesday, 16 February 2011

Visual Studio 2010 Report Viewer - Object Datasource

I have had no end of issues trying to get the ReportViewer control in Visual Studio to work with business objects in a Web Application (not website)

When I tried to create a new report the datasources window would never pick up my business objects. Here is a solution that I finally got working.

Create a new class library project to host reports. This library can contain the object datasources added by using the Data menu, then Add New Datasource. The reason I could not do this in the Web Application is because this menu item is missing for Web Applications!

Anyway, once you have added the datasources, add a new report to the reporting class library, here it will have access to all of the datasources (my data sources are actually business objects in another class library) This report needs to be set as an embedded resource.

Once you have the report and datasources ready its time to add the ReportViewer to an aspx page. I ran into a problem here when setting the ReportViewer.LocalReport.ReportEmbeddedResource. It looks like the report viewer looks for the resource in the current assembly. Luckily this can be resolved by adding a helper class to the reporting library to set the embedded resource from within the library.

Here is the code for the helper function in the reporting library

and here is how you would use it in the aspx page
Update for .net 4.0 I had an issue where the report would not load after being compiled in .net 4.0. The message read: 'The report definition for report 'xxx.rdlc' has not been specified' even though it was clearly in the assembly. Changing the way the Embeded resource was loaded seemed to fix the problem with the following simple change to the SetReportEmbeddedResource method in the reporting assembly.

Friday, 11 February 2011

How to resize and Existing Virtual Box Image (.vdi)

Start off by creating a fresh new drive of the size you’re after using the VirtualBox user interface. Then, locate both the your old, smaller HD and the new, larger one and run the following command in the command prompt.

After some progress indicators have come and gone your hard disk should have been cloned to the larger one. You now need to use some software to expand your drive partition into the new space. Vista and W7 have this feature built in to Disk Management so you can just opt to extend the partition to fill the remaining space.

How to remove and EFI System Partition from a Hard Disk

If you have an external hard disk that has an EFI system partition (usually about 200MB) and want to remove it then this can be achieved by using DISKPART from the command prompt.

1. On the command prompt type diskpart
2. On the new diskpart prompt, type list disk. Note the Disk ### column.
3. Type, select disk ### (with ### being the partition you wish to delete. Usually partition 0 and with those 200 MB of size)
4. Finish by typing, clean.

Friday, 4 February 2011

SQL Server - Shrink Transaction Log with DBCC

If you need to shrink an SQL Server database transaction log the following command can be used.