Mohamed Sharaf's Blog

How to handle expired SQL logins’ passwords in client code

Microsoft is always advise customers to use the new security features for SQL Logins in SQL Server. As you know SQL Logins are the logins where the login name and password are saved inside SQL Server and SQL Server is responsible for authenticated this login. We don't depend on Windows to validate them. Of course Windows authentication is more recommended as it's more secure but many times the customer has to use SQL login.

Starting SQL Server 2005, SQL Logins can have a policy inherited from the domain policy or the local machine policy so we can control the complexity of the password and how often this password will be expired. For more information about the password policy, refer to http://msdn.microsoft.com/en-us/library/ms161959(SQL.90).aspx

But usually when I recommend customers to use password expiration, they are concerned about their live application and how the application would respond the situation when the password is expired. I wrote a sample application by C# to handle this. For unmanaged code, you can follow up the instructions in this article.

To use managed code to respond to the password expiration, first we need to detect when the password expires. This would be by checking the SQLException error number, it's number 18487.

To change the password, I'm using SQL Server Management Objects (SMO) I'm assuming the structure of the connecting module would be like this

1. Try to connect normally to SQL Server, using SqlConnectionStringBuilder class to build the connection string. I'm assuming that the client saves the password in a protected storage so we need a function like getPassword()to get the password from this storage
2. If the password is expired (error 18487), the application is responsible to generate a new password generateNewPassword()and use the ServerConnection class to change the password.

I wrote pseudo implementation for getPassword() and generateNewPassword()but in real code, you should add your own algorithm.

Here's the code. It's implemented as a console application

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data.SqlClient;

using Microsoft.SqlServer.Management.Smo;

using Microsoft.SqlServer.Management.Common;

namespace TestSQLConnection

{

class Program

{

static void Main(string[] args)

{

SqlConnectionStringBuilder builder=new SqlConnectionStringBuilder();

builder.DataSource="myserver\\sql2k5";

builder.InitialCatalog="AdventureWorks";

builder.UserID="test";

builder.Password=getPassword();

SqlConnection conn = new SqlConnection(builder.ConnectionString);

try

{

Console.WriteLine("Trying to connect to SQL Server...");

conn.Open();

Console.WriteLine("Connection succeeded Connected to server version {0}, \n wiating your command...",conn.ServerVersion);

Console.ReadLine();

}

catch (SqlException ex)

{

Console.ForegroundColor = ConsoleColor.Green;

if (ex.Number == 18487) //the password expired

{

Console.WriteLine("the login password has expired, do you want to generate a new passwoed and use it(type 'Y' or 'N')");

string answer=Console.ReadLine();

if (answer == "Y" || answer=="y")

{

string newPassword = generateNewPassword();

ServerConnection srvConn = new ServerConnection();

srvConn.ServerInstance = "myserver\\sql2k5";

srvConn.LoginSecure = false;

srvConn.Login = "test";

srvConn.Password = getPassword();

srvConn.ChangePassword(newPassword);

Console.WriteLine("Password has changed");

savePassword();//save the new password for next connection

System.Threading.Thread.Sleep(3000);//give SQL server a chance to change the password and be ready

Console.WriteLine("Trying to connect to SQL Server again...");

builder.DataSource = "myserver\\sql2k5";

builder.InitialCatalog = "AdventureWorks";

builder.UserID = "test";

builder.Password = getPassword();

conn = new SqlConnection(builder.ConnectionString);

conn.Open();

Console.WriteLine("Connection succeeded Connected to server version {0}, \n wiating your command...", conn.ServerVersion);

Console.ReadLine();

}

else //close

{

System.Diagnostics.Process.GetCurrentProcess().CloseMainWindow();

}

}

else

{

Console.ForegroundColor = ConsoleColor.Red;

Console.WriteLine("error while opening connection");

Console.WriteLine("Error No: " + ex.Number);

Console.WriteLine("Message : " + ex.Message);

Console.ForegroundColor = ConsoleColor.White;

Console.ReadLine();

}

}

finally

{

if (conn != null && conn.State == System.Data.ConnectionState.Open)

conn.Close();

}

}

internal static string getPassword()

{

//you should implement the logic to get the password from encrypted storage

return "test";

}

internal static void savePassword()

{

//you should implement the logic to save the password in encrypted storage

}

internal static string generateNewPassword()

{

//you should implement the logic to randomly generate new complex password

return "test";

}

}

}

Moving the tempdb to unknown location

One of my friends called me for a problem at a customer site. The problem simple is that the customer wanted to move the tempdb in SQL Server 2005 to a new location but because of a typo the new path of the file is pointing to a folder not a file. Something like this "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\"

The alter statement would be something like this

alter database tempdb

modify file (name=tempdev, fileName='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\')

The result of this statement would be

The file "tempdev" has been modified in the system catalog. The new path will be used the next time the database is started.

So next time SQL Server tries to access the tempdb it will fail and the problem comes worse if you restarted the SQL Server, the Server won't start and this message will be logged in the Application Event log

FCB::Open: Operating system error 3(error not found) occurred while creating or opening file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\'. Diagnose and correct the operating system error, and retry the operation.

So what would be the solution of something like this?

I've tried this scenario and worked, I thought it would be better if I published it so if someone faced this situation (although, it's very rare situation) it would help.

• First we need to start SQL Server with minimum configuration in console mode; go the SQL Server binary folder (commonly in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn)
• Open a console window and run this command

  sqlservr.exe –c –f –m

So this starts SQL Server in console mode (not a service) and with minimum configuration and also with single user mode. You should see some messages like you see when you open the error log file

• Open another console window and run this command

  Sqlcmd /A

  This will connect to the default instance on the machine using windows authentication though Dedicated Administrator Connection (DAC)

  After logging you can alter the tempdb to add the path of the correct file; like this.

alter database tempdb modify file (name=tempdev, fileName='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ tempdb.mdf')

Now return back to the first console window where you started the SQL Server and press Ctrl+C this will shutdown the server

Start SQL Server normally and the problem is now solved J

MS File Encryption

As I promised in my last post, here's the MS File Encryption program (MS stands for Mohamed Sharaf not Microsoft of course ;)) based on the MSSecurity Library. The application is pretty simple; you can encrypt a single file or entire folder.

The application adds the extension MSEnc to the file when encrypting and remove it when decrypting just to identify which files are encrypted and which not in case you are using it to periodically encrypt your secret folder so it won't re-encrypt encrypted files again. The application is bundled with help document and you can get it directly here.

Download the File Encryption program HERE.

Please have a nice encryption J

Back to MSSecurity library again

If you have seen my previous post about the class library that I developed to encrypt text or files using symmetric encryption (Rijndael algorithm). So you would already know that it was developed using .NET Framework 1.1. I've upgraded the library to .NET Framework 2.0 and fixed a bug I discovered in the decryption methods.

I already described the methods in this library before so allow me to copy-and-paste them from my previous post

The library called MSecurityLibrary and it consists of only two classes

Encryption: does the symmetric encryption using Rijndael algorithm and a key size of 256 bits.

Hashing: hash any string with MD5 hashing algorithms

Encryption class has 11 public methods. I'll describe the main functions here.

public void encryptFile(string sourceFile,string distinationFile,string password)

public void encryptFile(string sourceFile,string distinationFile,byte[] key)

These two methods encrypt a file using either a password or an array of bytes as a key. You can use the generateKey() method to generate that key and save it in a file (usually should be put in a floppy disk to make it more secure).

public void decryptFile(string sourceFile,string distinationFile,string password)

public void decryptFile(string sourceFile,string distinationFile,byte[] key)

These two methods are the opposite of the others. They decrypt a file using either a password or a key.

public string encryptString(string inputString,string password)

public string encryptString(string inputString,byte[] key)

These two do the same but for strings. They take inputString parameter which will be encrypted using a password or a key

public string decryptString(string inputString,string password)

public string decryptString(string inputString,byte[] key)

The opposite to the previous two methods,

Hashing class has only one public method

public string HashStringWithMD5(string password)

This method takes a string and hashes it using MD5 algorithms and returns a string. This method is very powerful if you want to hash passwords in a database in case you want to save them secured.

The new version of the library can be found here. If you want to take a look at the source code, you will find it here.

I developed a windows application to encrypt and decrypt files or entire folders based on this library and I'm planning to post it with the full source code in my next post (God Willing).

See you in the next post sooooon J

//Mohamed Sharaf

Visual Studio Team System Survey

Visual Studio product team has recently launched this survey. Take this opportunity to say what you like and you don't in the product.

You can request any new features or add the comments you like.

The link to the survey is http://c2.microsoft.fr/0d26d4f2ccc64f449b07a379372d72b5/?elng=1033

</Mohamed>

 

Cross post with Master pages

I got a feedback for the Master Pages post asking about mixing master pages with cross post. Here's the scenario.

We have a content page that has one textbox and one button, the page code would be like this

<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" AutoEventWireup="false" CodeFile="master_form.aspx.vb" Inherits="master_form" title="Untitled Page" %>

<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">

<asp:TextBox ID="id" runat="server"></asp:TextBox>

<asp:Button ID="Button1" runat="server" PostBackUrl="~/result.aspx" Text="Button" />

</asp:Content>

As you can see, the button posts to another page called result.aspx.

When you get the data posted using Request.Form("id"), this would result in empty string because there's no entry with this name in the form collection in the form collection. Because the content page is merged with the master page so the id of the textbox would be ctl00$ContentPlaceHolder1$id (ctl00 is the form element, and contentPlaceHoder1 is the id of the contentPlaceHolder) so if you would like to get it from the form collection, you would write it in the form of Request.Form("ctl00$ContentPlaceHolder1$id").

This technique is not appropriate as you can see, so the most appropriate technique is to use the PreviousPage property. PreviousPage is a property for the Page class that has a reference to the previous page if you are doing Server.Transfer or Cross Posting.

You can use PreviousPage and FindControl method to get any control you have on the previous page like this code snippet.

Dim ctlTextBox As TextBox = Nothing

If Not Page.PreviousPage Is Nothing Then

ctlTextBox = CType(PreviousPage.Form.FindControl("ContentPlaceHolder1").FindControl("id"), TextBox)

End If

Hope this helps J

How to move objects from Schema to another in SQL 2005

If you are building CLR stored procedure and you want this stored procedure to be part of a schema called xyz. When you write the name of the procedure in the SqlProcedure attribute "xyz.myproc" and deploy this procedure, the visual studio deployed it with dbo schema and the name of the procedure will be dbo.xyz.myproc.

In this case and in many other cases, you may need to move the procedure to the xyz schema. Fortunately you can do this pretty simple with only 1 SQL statement. You can use Alter Schema TSQL command for this. The syntax as following

ALTER SCHEMA schema_name TRANSFER object_name

schema_name is the name of the schema that you want the object to be transferred to (xyz in our case)

object_name is the full name of the object including its schema (dbo.xyz.myproc in our case)

Note:

Unfortunately, this TSQL statement can't deal with multi parts name (names with more than one dot) so per in mind to use names with no dot in the middle (i.e. dbo.xyz_myproc)

I think this workaround is pretty easy.

 

Changing application settings programmatically in .NET Framework 2.0

As I promised to provide a component to enable you to change the values of the application-scope settings in .NET Framework 2.0, I'm now providing it.

It's a very simple dll written by C#, you can find the Source code here.

To use this dll you will need to get instance from the SettingWriter class as following

ApplicationSettingsWriter.SettingsWriter writer = new ApplicationSettingsWriter.SettingsWriter();

Then use the indexes of this object to change the value of the settings like this

writer["sampleSettings"] = "Omar";

In this example, "sampleSettings" is the key of the setting in the app.config. In Visual Studio 2005, you can access your settings visual by opening the properties window of the project (see figure below).

Writing the previous line indeed changes the value of the app.config but if you tried this line to retrieve the value, you wouldn't get your change

MessageBox.Show(Properties.Settings.Default.sampleSettings);

That's because .NET Framework loads the application settings with the loading of the application domain and caches them for performance. If you need to update this cache, you need to call the Reloud() method as following

Properties.Settings.Default.Reload();

You can download the binary from here and the source code from here.

Best regards,

Mohamed

Using application/user settings in C# 2.0

This is a quick post for using application or user settings in C# 2.0. I found most of the articles dealing with it in VB which is pretty easy.

All you need to access a setting called "ServerName" in Visual Basic is this line

My.Settings.ServerName="myServer"

For more information about settings you can refer to MSDN @ http://msdn2.microsoft.com/en-us/library/bc6ws923(VS.80).aspx (all samples in VB)

In C#, to access the application/user settings you need to use this line

String myserver=Properties.Settings.Default. myServer;

If you want to write to this

Properties.Settings.Default. myServer="myServer";

But take care that you can't change application settings in code. You can only change user settings. To change the application settings, you need to write your own code to open the configuration file as any xml file and modify it. I've written a simple component to do this which I'll post later this week.

Misleading error message when you create foreign key constraint

When you create new foreign key constraint in SQL Server 2005 and this constraint conflict with the data already exists in the tables, you will get an error message that might be misleading. Let's say that you have 2 tables (depts and employee) and you want to create a foreign key constraint in the employee table that references the depts table. If you wrote this script

ALTER TABLE dbo.employees ADD CONSTRAINT

    FK_employees_depts FOREIGN KEY

    (

    deptID

    ) REFERENCES dbo.depts

    (

    ID

    ) ON UPDATE NO ACTION

     ON DELETE NO ACTION

And you have a raw in the employee table that has deptID column with a value that doesn't have a match in the depts. Table, you will get this error message

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_employees_depts". The conflict occurred in database "testing", table "dbo.depts", column 'ID'.

You may think that the foreign key constraint created, not it's not. This error message equivalent to the this error message in SQL 2000

Foreign key 'FK_employees_depts' references invalid column 'deptID' in referencing table 'employees'.

Msg 1750, Level 16, State 0, Line 1

Could not create constraint. See previous errors.

So you need to resolve data conflict in the table before creating the foreign key.

Hope that helps J

How to add null value in Management Studio (SQL Server 2005)

Updating database tables using Management Studio is a the easiest way during the development phase. If you do so, you probably need sometimes to update column to add a null value instead of the current value.

To do so you just need to press CTRL+0

It's also valid for Enterprise Manager for SQL 2000.

 Enjoy :)

>> Mohamed Sharaf

Using test certificate with Reporting Services 2005 to establish SSL connection

If you are planning to publish your Reporting Services site to the internet then you need to secure the communication between the server and the clients. The industry standard to do so is to buy an SSL certificate from a public certificate authority (CA) like VeriSign. This would cost you much money especially that you have to pay for each physical server.

The other solution that would be appropriate if you are publishing your reports to your company's employees is to use a certificate issued by you or a "testing certificate".

To do so you need to do the following steps

• 1. Install Certificate Authority (CA) on this computer

By installing Certificate Authority Service (CA), you would behave like VeriSign so you can issue certificates to other if they requested a certificate from your server. To install Certificate Authority on Windows 2003, follow these steps

Go to Control Panelà Add/ Remove programs à Add/ Remove windows components

Check on Certificate Services and follow the steps or the wizard as following

• a) Choose Stand-alone root CA because it's necessary for Reporting Services to trust the certificates issued from this CA.


• b) Write the Common Name (CN) of the new CA.


• c) The wizard will restart IIS and it's better to reboot the machine after installing the new CA
• 2. Request a certificate using IIS MMC from this CA

From IIS, right click on the web site that has Reporting Services installed on it and choose properties. Open the "Directory Security" tab and press "Service Certificates" button.

Choose "Create new Certificate" then Next

From the second screen in the wizard choose "Prepare the request now but send it later" then click Next

In the Name and security Settings page, write a name of your certificate.

In the Organization Information page, write your company name and the department.

In the "Your Site's Common Name" page, write the common name of your site. Take care of this step because it's very important this name should be the name of your site. i.e. if the site full name is Extranet.MyCompany.com so the common name should be Extranet.MyCompany.com

At the end of this wizard, it will save the request in text file.

 

 

• 3. Issue the certificate

Now, open the Certificate Authority (CA) console (Administrative tools à Certification Authority.

Right click on the CA name in the console and choose "Submit new request"

Browse to the certificate request file that you created using IIS wizard and choose it.

You will find it under bending Requests folder, right click on it and choose issue. You will find it under the Issued certificates folder.

Right click on the certificate and choose "Export Binary Data". Choose cer extension to the file and save it.

• 4. Install Certificate on IIS

Now Open again IIS management console and from "Directory Security" tab of the web site properties, choose "Server Certificate". In the wizard choose "Process the bending request and install certificate".

Choose the file that you saved using Certificate Authority and complete the wizard.

Now you have a certificate ready for you web site, if you want to restrict access to SSL connections only for the whole site or any particular web application, do the following.

            From the web site/application properties choose directory security tab then in the "Secure Communication" box, choose Edit. Check require secure channel (SSL).

• 5. Set the Reporting Services to use this Certificate

Finally, you need to configure Reporting Services to work over SSL.

Open Reporting Services Configuration from the SQL Server 2005 group.

Choose Report Server Virtual Directory section.

Mark the checkbox "Require Secure Socket Layer Connections (SSL)"

In the "Require For" drop down, choose the appropriate method for your usage the description of them is

1 - Connections only

2 - Report data

3 - Entire Web service API

The levels are cumulative. Level 3 is the most secure and 1 is the least secure one. Form more information about them please refer to http://msdn2.microsoft.com/en-us/library/ms154709.aspx

In the certificate name text box, add the certificate common name as you wrote in step 2 so it should be in our example Extranet.MyCompany.com

Now restart the machine then begin your encrypted browsing J

• 6. Side issues.

I've faced some side issues during the implementation of SSL for Reporting Services 2005. Like the following

You need to reboot the server after setting the certificate

• The common error message in SSL with reporting services is "The underlying connection was closed: Could not establish secure channel for SSL/TLS" This error message means that the reporting web application code doesn't trust the certificate of the Reporting Services web service.
• Note:

When you browse the reporting web application, you actually are calling the XML Web Service of Reporting Service because the web application is calling it.

That's why it's required to install the certificate as trusted certificate in all your servers if you have server farm.

 

• When you want to deploy your reports from your development machine to the server, the deployment fails with the error "The underlying connection was closed: Could not establish secure channel for SSL/TLS"

You need also to install the certificate as trusted certificate in the development machine, the easiest way to do so is the following:

Browse to the reporting site using IE Click on view certificate button in the warning window in case of IE6 or the red area at the top in case of IE 7 (see image below) In the view certificate window, click on Install certificate In the certificate store screen, choose "Place all certificate in the following store" Click on browse and choose Trusted root certificate authorities. You will get a warning that Microsoft can't trust this certificate, choose yes to install it. You now trust the certificate but you need also to trust the certificate authority which issued this certificate. Browse to the same site again (you may need to open another instance of IE) and click on view certificate then choose certificate path tab. This will show you the issuer of the certificate. Click on the issuer and choose view certificate (at the bottom) and install the issuer in the Trusted root certificate authorities

 

• You get a warning when you browse to the Reporting site from Internet Explorer.

You need to install the certificate as discussed earlier.

Are you using IE7 and have a problem in accessing some site?

Internet Explorer 7 is the newest version of Internet Explorer(IE) will be launched on Windows Vista time. It will also be available for XP SP2 and 2003 SP1 users. It has many fancy features like new rendering engine(you will love it especially in LCD screens), Built in RSS aggregator, new enhanced security model and new printing engine. You can try the Beta 3 now at http://www.microsoft.com/windows/ie/downloads/default.mspx

Some early testers faced a problem that they couldn't browse some sites they used to browse using IE 6 that's because these sites are searching for IE 6 user agent header. If you are using IE 7 then your user agent header will be like this

User-Agent=Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; .NET CLR 1.1.4322; .NET CLR 2.0.50727)

Overcoming this problem depend on who you are, are you a web developer who want to allow IE7 users to browse your site without a problem or you are a user that couldn't browse your favorite site after installing IE7.

If you are a Web Developer:

Please refer to MSDN article at http://msdn.microsoft.com/library/default.asp?url=/workshop/author/dhtml/overview/browserdetection.asp titled "Detecting Internet Explorer More Effectively"

If you are a user who installed IE7 and you want to browse your favorite sites like before:

You need to change a key in your registry to make your IE 7 simulate the user-agent header of IE6. Fortunately, this is pretty simple. All you need is to run the reg file included in http://www.fiddlertool.com/useragent.aspx. Download and run the file in the link "Internet Explorer 6". Restart your IE and everything will work fine with you :)

have a nice browsing,

Mohamed Sharaf

Adding custom code to Local Reports in Visual Studio.NET 2005 (Problems & Solutions)

If you are one of the people who used and enjoyed SQL Server Reporting Services (SSRS) in SQL 2000 and you wanted to use it in your windows/web applications without the server side components (just like what you are doing with Crystal Reports). So you would be much exited about the new ReportViewer Controls. ReportViewer controls are two controls for Windows and Web applications that can either show reports produced by SQL Server Reporting Services or process and render reports locally.

 

These reports are called Local Report and it's intended to provide reporting features without SSRS. You can use them without having a license for SSRS but be aware that you would lose some features like

• Report parameters in client report definitions (.rdlc) do not map to query parameters. There is no parameter input area in a client report definition that accepts values that are subsequently used in a query.
• Client report definitions do not include embedded query information. You must define data sources that return ready-to-use data for the report. So you have to provide a DataSource for your report like a DataSet, DataTable…
• Browser-based printing through the RSClientPrint ActiveX control is not available for client report definitions that run in the ReportViewer Web server control. The print control is part of the report server feature set.
• You would loose all collaboration activities of SSRS like scheduling to send reports to some users at specific time

 

I used ReportViewer for Windows forms in small report in which I had to add custom code in it and I had some problems that I would like to share with you solutions for them.

First we would go quickly through the process of creating simple report as following:

1)      Create a new windows forms project and open its form. Drag the ReportViewer control from the Data tab as shown in the image below

2)      Use the smart tag tasks to dock the report in all the form (you can use properties window as well). Then from the smart tag choose "Design new report". If you didn't see this option you can simply create new report. From solution explorer, right click on the project name then choose "add new item" then choose report.

3)      The report file would have the extension "rdlc" not "rdl" as it would be in server reports.

4)      Local Reports need to have a data source so go to Data Sources window and create your data source (let's say that it will get all Category name and description from categories table in Northwind)

5)      From the ToolBar window, drop a table inside the report

6)      From the Data Source window, drag & drop the CategoryName and Description fields to the details row of the table. Now you finished creating the report. Let's bind it to the ReportViewer

7)      Return back to the ReportViewer and from the smart tag menu choose, select your report name from the drop down menu.

8)      Run the application.

 

Now you have the report. Let's go to add custom code to the report.

 

Why would you add custom code?

You may want to add custom code to the report to do more actions than what's provided with the report functions. So it gives you a base for extending your report. You may want to add custom code to implement simple string manipulation task or sophisticated data access manipulation.

 

Cod added to Local Report can be one of two types:

            Embedded Code: Added directly inside the rdlc file. If you open the file in notepad, you can see the code inside the <Code> tag.

            Custom Assemblies: You can add a reference in external assembly and use functions inside it.

 

How to add a custom code in a local report?

According to MSDN library, you add it as following:

Embedded Code:

1. On the Report menu, click Report Properties.
2. On the Code tab, in Custom code, type the code.

Custom Assemblies:

1. On the Report menu, click Report Properties.
2. On the References tab, do the following:
3. In References, click the add (...) button and then select or browse to the assembly from the Add Reference dialog box.
4. In Classes, type name of the class and provide an instance name to use within the report. That's in the case of instance members, in the case of static (shared in VB) members, you don't need to add anything in Classes.

 

What's the code that I can add?

You can add class members only (properties, methods, fields) but you can't add classes because these members are encapsulated in a class at runtime, this class called Code class.

 

Take a look at this code snippet.

 

Public ReadOnly Property getSomeData() As String

        Get

            Return sharedMember

        End Get

    End Property

 

    Dim sharedMember As String = "Omar"

 

    Public Function toUpperCase(ByVal s As String)

        Dim conn As New System.Data.SqlClient.SqlConnection()

        Return s.toUpper()

    End Function

 

You can use this code inside a textBox or a table column as following =Code.toUpperCase(Code.getSomeData)

 

What are the namespaces that I can use inside my custom code?

All .NET namespaces can be used inside your custom code but be aware that by default only Microsoft.VisualBasic, System.Convert, and System.Math namespaces are referenced inside your reporting custom code.

 

Now let's jump into the problems.

 

Adding code that uses types that are not in these namespaces (Microsoft.VisualBasic, System.Convert, and System.Math)

Let's say that you are using a code that access SQL Server database to get some data to use it inside your report. You are using SqlConnection in your code. You would receive an error like this

There is an error on line 9 of custom code: [BC30002] Type 'System.Data.SqlClient.SqlConnection' is not defined.

Because System.Data.SqlClient resides in the System.Data.dll assembly and this assembly is not referenced by default by the report custom code. To solve this problem you need to add a reference to the System.Data.dll assembly in the References tag in the report properties window.

But this is not the end of the story; you would face another problem which described in the next step

 

How to make an assembly trusted for your report code

Because Local Reports like SQL Server Reporting Services reports are written using open format (Report Definition Language (RDL)). Any user can add any code or reference any assembly from your rdl file at the production environment which would cause a BIG security hole. You need to mark these assemblies as trusted inside your Windows Application code. Or you would get an error like this

The report references the code Module 'System.Data, Version=2.0.0.0, Culture=neutral, Publickey Token=b77a5XXXXXXXX', which is not a trusted assembly

 

You need to use the AddTrustedCodeModuleInCurrentAppDomain method of the LocalReport class. You can add the following line at the Form_load event handler of your windows application.

 

this.reportViewer1.LocalReport.AddTrustedCodeModuleInCurrentAppDomain("System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089");

 

And again it's not the end of the story; you will face another problem ;). Please be patient with me because I faced all these problems sequentially and I didn't find any resources to help me.

 

What are the Code Access Security (CAS) permissions assigned to any code running inside LocalReport by default

By default the only permission bound to the code running inside the LocalReport is ExecutePermission So your code can't access file system, databases, registry… unless you give it explicitly the required permissions.

For example, if you are trying to connect to a database from your code, you will get this error

Request of the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKey Token=b77a5c561934e089' failed.

 

To give the assemblies running in the report the appropriate permissions, you need to use the ExecuteReportInCurrentAppDomain Method of the LocalReport class. Which causes the expressions in the report to be executed inside the current appDomain instead of executing them in a sandbox.

 

The ExecuteReportInCurrentAppDomain method takes one parameter of the type Evidence.

In the following example I'm using the evidence associated with my current appDomain so the report code will have all permissions that I have in my application

 

this.reportViewer1.LocalReport.ExecuteReportInCurrentAppDomain(AppDomain.CurrentDomain.Evidence);

 

By working on these steps, your code will run smoothly inside your report.

 

 

For the people who are trying to move their code in separated assembly and reference it from their report, they would face another problem.

If you are following the steps mentioned in MSDN regarding how to reference custom code (mentioned above). You will end up with this message

Error while loading code module: ‘ClassLibrary1, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null’. Details: Could not load file or assembly 'ClassLibrary1, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. The system cannot find the file specified.

 

After a lot of trials with this error, I found that I have to do the following.

1. Select the report file from Solution Explorer, Go to Properties Window and change its BuildAction to Content
2. Set Copy to output Directory property to Copy always or Copy if newer
3. In the form designer class (the partial class created by Visual Studio according to naming schema <FormName>.Designer.CS or <FormName>.Designer.VB) replace the line which instruct the ReportViewer to load the report file from resource file with another line that instruct the ReportViewer to load the report from current directory.
4. So this line

this.reportViewer1.LocalReport.ReportEmbeddedResource = "testLocalReport.Report1.rdlc";

5. With this line

this.reportViewer1.LocalReport.ReportPath = "Report1.rdlc";

6. Copy the dll that you are referencing to the Debug or Release directory of your application
7. Make the line that executes the report in the current appDomain first, then add the lines that trust the referenced assembly and all assemblies referenced by this assembly (see above)

 

I hope this would help you to really enjoy VS 2005 features

 

//Mohamed

 

 

Encrypting configuration files using protected configuration

One of the cool security features in ASP.NET 2.0 is the ability to parts of web.config. Web.config encryption uses XML encryption standards at its core.

 

Protected configuration uses a class derived from the abstract class ProtectedConfigurationProvider. .NET framework has shipped with two providers RSAProtectedConfigurationProvider which uses TripleDES and RSA encryption and DPAPIProtectedConfigurationProvider which uses Windows Data Protection API (DPAPI)

 

For more comprehensive information about this topic, please refer to MSDN

 

Protected configuration in action:

One of the most common uses of the protected configuration is to encrypt connection strings in web.confg (that's one of the reasons for creating a separate tag for connection strings instead of adding it in appSettings tag).

Let's say we have a connection string tag like this

<connectionStrings>

<add name="advWorks" connectionString="Data Source=.\yukon;Initial Catalog=AdventureWorks;User ID=webUser;pwd=my_P@ssw0rd" />

</connectionStrings>

 

Adding this connection string as plain text is not the best practice for web application security and this might cause serious hacking problems. To encrypt this tag only, you could use the encryption classes in .NET framework or use the handy tool ASPNet_regiis.exe.

 

Assuming that this application located in http://localhost/testwebCS2, we could use ASPNet_regiis.exe as following

 

aspnet_regiis -pe connectionStrings -app /testwebcs2

 

This line will use RSAProtectedConfigurationProvider to encrypt the tag connectionStrings in the application called /testwebCS2.

 

Then this tag would be as following (I omitted most of the cipher base64 strings to preserve space)

 

<connectionStrings configProtectionProvider="RsaProtectedConfigurationProvider">

        <EncryptedData Type="http://www.w3.org/2001/04/xmlenc#Element"

            xmlns="http://www.w3.org/2001/04/xmlenc#">

            <EncryptionMethod Algorithm="http://www.w3.org/2001/04/xmlenc#tripledes-cbc" />

            <KeyInfo xmlns="http://www.w3.org/2000/09/xmldsig#">

                <EncryptedKey xmlns="http://www.w3.org/2001/04/xmlenc#">

                    <EncryptionMethod Algorithm="http://www.w3.org/2001/04/xmlenc#rsa-1_5" />

                    <KeyInfo xmlns="http://www.w3.org/2000/09/xmldsig#">

                        <KeyName>Rsa Key</KeyName>

               </KeyInfo>

                  <CipherData>

                      <CipherValue>YojkA1KgIR1nnThk=</CipherValue>

                    </CipherData>

                </EncryptedKey>

            </KeyInfo>

            <CipherData>

                <CipherValue>8h+tauv00O52DdGHFKSv =</CipherValue>

            </CipherData>

        </EncryptedData>

    </connectionStrings>

 

As you can see in the previous XML, the data itself encrypted using the symmetric encryption algorithm called Triple DES. The key used to encrypt the data is embedded but it's also encrypted using RSA.

 

But where's the key to decrypt that key?

Good question :). The RsaProtectedConfigurationProvider uses the machine account or the user account to encrypt the keys and save them in a file which called "key container". To use the protected configuration with ASP.NET we should not use the user account to encrypt the keys because we need to be logged in with that user to be able to open that key container.

 

Key container for the machine account usually saved in C:\Documents and Settings\All Users\Application Data\Microsoft\Crypto\RSA. And the ASP.NET worker process identity (ASPNET user in XP/2000 or Network Service in case of 2003) should have access to these files to be able to decrypt it or you would get this error message

 

"Failed to decrypt using provider 'RsaProtectedConfigurationProvider'. Error message from the provider: The RSA key container could not be opened"

 

Fortunately the ASPNet_regiis tool gives us the option to add users to the ACL of the key containers using the –pa parameter.

For example to give access to the ASPNET user

aspnet_regiis -pa "<key container name>" "ASPNET"

 

So, how to get the key container name?

By searching in machine.config (usually in C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\CONFIG) we would find this section

<configProtectedData defaultProvider="RsaProtectedConfigurationProvider">

    <providers>

      <add name="RsaProtectedConfigurationProvider" type="System.Configuration.RsaProtectedConfigurationProvider,System.Configuration, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" description="Uses RsaCryptoServiceProvider to encrypt and decrypt" keyContainerName="NetFrameworkConfigurationKey" cspProviderName="" useMachineContainer="true" useOAEP="false" />

      <add name="DataProtectionConfigurationProvider" type="System.Configuration.DpapiProtectedConfigurationProvider,System.Configuration, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" description="Uses CryptProtectData and CryptUnProtectData Windows APIs to encrypt and decrypt" useMachineProtection="true" keyEntropy="" />

    </providers>

  </configProtectedData>

 

The keycontainerName of the RsaProtectedConfigurationProvider is "NetFrameworkconfigurationKey" (it's good practice to change it in the production servers). So the aspnet_regiis would be as following

 

aspnet_regiis -pa "NetFrameworkConfigurationKey" "ASPNET"

 

What if I'm using a server farm?

In the server farms environment, you can simple use aspnet_regiis to create and export key container to distribute it to the whole server farm.