SSRS: Performance Tuning

1. Fix problem with slow booting up of report server:
- Open the report config file
- modify RecycleTime parameter from the default 720 to 28800.

SSIS: Logging Table

SELECT top 1000 *
FROM [dbo].[sysssislog]
order by starttime desc

SSIS: VBScript for Excel inserting 0 and get columns

/*
Microsoft SQL Server Integration Services Script Task
Write scripts using Microsoft Visual C# 2008.
The ScriptMain is the entry point class of the script.
*/
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Collections.Generic;
using System.Data.OleDb;
using System.Diagnostics;
namespace ST_f1f4f90e7d8e4a25b9330e1d946d8c95.csproj
{
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
#region VSTA generated code
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion
/*
The execution engine calls this method when the task executes.
To access the object model, use the Dts property. Connections, variables, events,
and logging features are available as members of the Dts property as shown in the following examples.
To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;
To post a log entry, call Dts.Log("This is my log text", 999, null);
To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true);
To use the connections collection use something like the following:
ConnectionManager cm = Dts.Connections.Add("OLEDB");
cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";
Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
To open Help, press F1.
*/
public void Main()
{
int error_flag = 0;
Dts.Variables["ErrorFileName"].Value = Dts.Variables["FileName"].Value.ToString().Replace(".xls", "_errors.xls");
//SET NUMERICS IN EXCEL FILE TO ZERO TO AVOID CRAPPY JET DRIVER INCORRECTLY SETTING COLUMN TYPES AT RUNTIME
string ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=" + Dts.Variables["FileName"].Value.ToString() + @";
Extended Properties=
""Excel 8.0;HDR=YES;""";
System.Data.OleDb.OleDbConnection ExcelConnection =
new System.Data.OleDb.OleDbConnection
(ConnectionString);
string ExcelQuery;
List numericExcelColumns = new List{
//"[AnnualSalary]",
"[Salaries]",
"[Overtime]",
"[Pay In Lieu Of Holiday]",
"[Severances]",
"[Employers Social Taxes]",
"[SPOT Bonus]",
"[STIP Bonus]",
"[Contractual STIP]",
"[Earnout STIP]",
"[Sales Commision Bonus]",
"[Taxable Value Of PSA Payout]",
"[Taxable Value Of Other Restricted Payout]",
"[Taxable Value Of LEAP Payout]",
"[Taxable Value Of Worldwide Ownership Plan On Exercise Payout]",
"[Taxable Value Of Executive Option Plan On Exercise Payout]",
"[Pensions - Defined Contribution Schemes]",
"[Pensions - Defined Benefit Schemes]",
"[Medical Insurance]",
"[Life Assurance]",
"[PHI/LTD]",
"[Critical Illness]",
"[Car - Lease Or Depreciations]",
"[Car Allowance]",
"[Other Car]",
"[Housing]",
"[School Fees]",
"[COLA]",
"[Home Leave]",
"[Club Membership]",
"[Relocation Expenses]"};
try
{
System.Data.OleDb.OleDbCommand ExcelCommand;
//UPDATE
ExcelConnection.Open();
ExcelCommand = new System.Data.OleDb.OleDbCommand();
ExcelCommand.Connection = ExcelConnection;
int nocols = numericExcelColumns.Count;
//Fill DataTable with columns information
OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", ExcelConnection);
DataSet myDataSet = new DataSet();
myCommand.Fill(myDataSet, "ExcelInfo");
//DataGrid1.DataSource = myDataSet.Tables["ExcelInfo"].DefaultView;
//DataGrid1.DataBind();
DataTable myDataTable = ExcelConnection.GetSchema("Columns");//, new string[] { "Test", "Tables" });
string columnName;
foreach (string excelColumn in numericExcelColumns)
{
//For every Column in the table
for (int i = 0; i < myDataSet.Tables[0].Columns.Count; i++)
{
//Get column name and type
columnName = myDataSet.Tables[0].Columns[i].ToString();
if ("[" + columnName + "]" == excelColumn)
{
error_flag = error_flag + 1;
break;
}
}
ExcelQuery = string.Format("update [Sheet1$] set {0} = '0' where {0} is null and EmployeeID is not null", excelColumn);
ExcelCommand.CommandText = ExcelQuery;
ExcelCommand.ExecuteNonQuery();
}
//ExcelQuery = "update [Sheet1$] set [SPOT Bonus] = '1' where len([SPOT Bonus]) = 0";//works
//ExcelQuery = "update [Sheet1$] set [SPOT Bonus] = '9' where [SPOT Bonus] = 1";//works
//ExcelQuery = "update [Sheet1$] set [SPOT Bonus] = '9' where [SPOT Bonus] is null";//works
//ExcelQuery = "update [Sheet1$] set [Overtime] = '0' where [Overtime] is null";//works
ExcelCommand = null;
ExcelConnection.Close();
}
catch (Exception e)
{
Debug.WriteLine(e.Message);
}
if (error_flag == 30)
{
Dts.TaskResult = (int)ScriptResults.Success;
}
else
{
Dts.TaskResult = (int)ScriptResults.Failure;
}
}
}
}

ASP. NET: Retrieve Excel file columns

protected void btnImportExcelFile_Click(object sender, EventArgs e) {
string strConn; strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=C:\\exceltest.xls;" + "Extended Properties=Excel 8.0;"; //You must use the $ after the object you reference in the spreadsheet OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [Tabelle1$]", strConn);
DataSet myDataSet = new DataSet(); myCommand.Fill(myDataSet, "ExcelInfo"); DataGrid1.DataSource = myDataSet.Tables["ExcelInfo"].DefaultView; DataGrid1.DataBind();
}

SSIS: Run packages from Stored Procedure

/***********************************************************************
Name : [USP_SSIS_Run_Packages]
Description : Run SSIS Packages. This package
is called by the website
Created by : John Ilett
Modified Date: 19 June 2009
Ex:
Exec [dbo].[USP_SSIS_Run_Packages] 'C:\HRDW\Data\Uploads\3\200912.xls'
***********************************************************************/
/**********************************************************************
Activate xp_cmdshell
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO
***********************************************************************/
ALTER PROCEDURE [dbo].[USP_SSIS_Run_Packages] (
@FileName nvarchar(200)
)
AS
BEGIN
DECLARE @ServerName VARCHAR(30), @ReturnValue int, @Cmd varchar(1000)
,@FilePath VARCHAR(100)
,@PackageName VARCHAR(50)
,@ParamMappings VARCHAR(MAX)
SET @ReturnValue = -1
SET @ServerName = 'localhost'
SET @FilePath = 'C:\HRDW\SSISPackageConfigs\VerifyFile.dtsx'
SET @PackageName = 'C:\HRDW\SSISPackageConfigs\VerifyFile.dtsx'
SET @Cmd = 'DTExec /F "' + @PackageName + '"' + ' /SET \Package.Variables[FileName].Properties[Value];' + '"' + @FileName + '"'
EXECUTE @ReturnValue = master..xp_cmdshell @Cmd--,NO_OUTPUT
RETURN @ReturnValue
SELECT @ReturnValue [Result]
--SET @Cmd = 'DTEXEC /sq "VerifyFile" /ser ' + @ServerName + '/Set \Package.Variables[User::FileName].Properties[Value];' + @FileName
--SET @Cmd = 'DTEXEC /F "' + @FilePath + '"' + ' /Set \Package.Variables[User::FileName].Properties[Value];' + @FileName
--SET @Cmd = 'DTEXEC /SER ' + @ServerName + ' /SQL ' + @PackageName + ' '
--SET @ParamMappings = '/SET Package.Variables[FileName].Value; ' + '''' + @FileName + ''''
--SET @Cmd = @Cmd + @ParamMappings
----SET @Cmd = 'DTExec /SER ' + @ServerName + ' ' +
----' /SQL ' + --+ '\BuildSCCDW ' --Location of the package stored in the mdb
------' /CONF "\\ConfigFilePath.dtsConfig" '
----' /SET \Package.Variables[ImportUserID].Value; ' +
----' /U "ASPNET" /P "password" '
--PRINT @Cmd
--EXEC sp_xp_cmdshell_proxy_account 'wpp1221\ASPNET_user', 'password';
END

ASP .NET: Website testing

SQL injection: To test for SQL injection bugs, peruse the application and find places where users can enter text, such as where the text is used to perform a lookup function, according to Breach. Then type a single quote character and some text: If the application shows an error message from your database, then you're likely housing an SQL injection bug.

Cross-site scripting (XSS): Find areas in your application that accept user input, such as a page where users can send in their feedback or reviews of a product, for example. Try submitting this text -- a less-than sign, the word "script," and then the greater-than sign (with no spaces in between): ! script !
If that text displays where you reload the page, then your site has an XSS vulnerability, according to Breach.

Session hijacking: If your application has a session identifier number in the URL -- which Breach does not recommend -- decrease that number by one and reload the page. The app has a session hijacking vulnerability if the app then "sees" you as a different user. And if you don't have a session identifier in the URL, load a plug-in onto your browser that lets you view and modify cookies, according to Breach, which sells Web application firewalls. "Look in the cookie for session identifiers and perform the same test," according to Breach.

ASP. NET: Web.Config File example

!add key="ReportServerURL" value="http://ServerName/ReportServer"/!

!add key="ReportFolder" value="ReportFolder"/! !/appSettings! !connectionStrings! !add name="HRDWConnString" connectionString="Data Source=.;Initial Catalog='HR DW';Integrated Security=True" providerName="System.Data.SqlClient"/! !/connectionStrings!

!trace enabled="false" requestLimit="10" pageOutput="true" traceMode="SortByTime" localOnly="true" /!
!customErrors mode="RemoteOnly" /!
!authentication mode="Windows"/!
!identity impersonate="true"/!

authorization

allow users = "*"/

/authorization

ASP .NET Security considerations

1. Use Session Cookies (not Cookieless sessions)
As Session Cookies store on Server side. Cookless ones mean hackers can get around through URL manipulation.

2. Use Server.HtmlEncode() method to encode any user input:

While this vulnerability has been hot-fixed, it is nevertheless important that you employ added precaution. One good method is to use the Server.HtmlEncode() method to encode all user inputs into HTML-encode strings:

Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles Button1.Click
lblMessage.Text = Server.HtmlEncode(txtName.Text)
End Sub

3. SQL Injection. If you pass username straight to where clause then hacker can display all users:

They can enter into the password:
xyz' union select @@servername, @@servicename, @@version --
or xyz' or UserId like '%

correct method:

Private Sub btnLogin_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) _ Handles btnsecureLogin.Click SqlConnection1.Open() Dim str As String = "SELECT * FROM Users WHERE " & _ "UserID=@userID AND Password=@password" Dim comm As New SqlCommand(str, SqlConnection1) comm.Parameters.Add("@userID", txtName.Text) comm.Parameters.Add("@password", txtPassword.Text)
Dim reader As SqlDataReader = comm.ExecuteReader() If Not reader.HasRows Then _ Response.Write("Login failed. Please try again") While reader.Read() Response.Write("Hello " & reader("UserName")) End WhileEnd Sub

4. Validate user inputs

Always check for length and wildcard characters.

5. Use hashing to store password:

A much better way to store passwords in your database is to use hashing. Hashing is a one-way process of mapping data (plain text) of any length to a unique fixed-length byte sequence. This fixed-length byte sequence is called a hash. Statistically, two different pieces of data would not generate the same hash. And a hash cannot be used to reverse-generate the plain text. In the case of saving passwords in the database, saving the hash value of each password is preferred over the saving the plain password. When a user logs in, the hash value of the password is computed and then compared to the hash value stored in the database. In this case, even if the database server is compromised, the hackers have no way of knowing the users’ real passwords (though he could still alter the hash value of a user’s password to one he generated himself and gain illegal access).
The following function shows how to use the SHA1 hash algorithm implementation in the .NET Framework:
Public Function ComputeHashValue(ByVal data() As Byte) As Byte()
Dim hashAlg As SHA1 = SHA1.Create
Dim hashvalue() As Byte = hashAlg.ComputeHash(data)
Return hashvalue
End Function
You could derive the hash value of a password like this:
Dim hashValue() As Byte
hashValue = ComputeHashValue(Encoding.ASCII.GetBytes(txtPassword.Text))

The hash value could then be stored in place of the user’s password.
Tip 5—Encrypt Sensitive DataASP.NET Web developers know that it is sometimes useful to store information such as database connection strings in the Web.config file rather than hardcode them in the application. Doing so allows the database server to be changed without modifying and recompiling the application. However, storing sensitive information such as the connection string (which may contain user information and password) in plain text format in Web.config file is not a very good idea, as Web.config is an XML document stored as a text file and thus easily accessed.
So, a safer way would be to encrypt the sensitive information and store the ciphertext into the Web.config file. There are two types of encryption algorithms that you can use:
Symmetric
Asymmetric Symmetric algorithms encrypt and decrypt information using a common key. It is a simple and efficient way of encrypting/decrypting information. However the use of a common key makes it less secure if more than one party needs to know the key.
Asymmetric algorithms encrypt and decrypt information using a pair of keys. This pair of keys is made up of a private and a public key. Data encrypted using the public key can only be decrypted using the private key and vice versa. Asymmetric algorithms are much more complex and are computationally expensive. However, it is also much more secure than symmetric algorithms.
Listing 1 shows the use of the Rijndael symmetric encryption algorithm implementation in .NET. Listing 2 shows the RSA asymmetric encryption algorithm implementation in .NET.
The functions shown in Listings 1 and 2 will allow you encrypt the sensitive data in your Web application, especially configuration and XML files. Listing 3 shows the supporting function used by the functions in Listings 1 and 2. The supporting function converts a string to a byte array. For example, it converts a string "1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16" to a byte array of {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16}.
For asymmetric encryption, you need to first create the pair of private and public keys:
'===========For Asymmetric use=============
Dim publicKey, privateKey As String
Dim RSA As New RSACryptoServiceProvider()
publicKey = RSA.ToXmlString(False) ' get public key
privateKey = RSA.ToXmlString(True) ' get private key
'===========Asymmetric Encryption=============
Dim cipherText as String = AsymmetricEncryption _
(txtAsyPlainText.Text, publicKey)
'===========Asymmetric Decryption=============
Dim plainText as String = AsymmetricDecryption _
(txtAsyCipherText.Text, privateKey)
For symmetric encryption, you need a 16-byte key and Initialization Vector (IV):
'===========Symmetric=============
Dim Key, IV as String
Key="1234567890123456"
IV ="1234567890123456"
Dim cipherText As String = SymmetricEncryption _
(txtSyPlainText.Text, Key, IV)
'===========Symmetric=============
Dim plainText as String = SymmetricDecryption _
(txtSyCipherText.Text, Key, IV)
Because SOAP messages are sent in plain text, Web services could also benefit from encryption. Instead of using SSL to protect the entire communication path (which is overkill), you could use encryption to protect sensitive information such as credit card numbers from prying eyes.


6. *Store secure information in registry (DB connection string UN and Password):

1. Download the aspnet_setreg.exe utility from http://support.microsoft.com/default.aspx?scid=kb;en-us;Q329290.

2. Create a new user account in your Windows machine. I have called it ASPNETUSER with a password of “secret.”

3. Add the !appSettings! element into your Web.config file. This setting saves the database connection string into Web.config:
!configuration!
!appSettings!
!add key="Distributor" value="workstation id=F16;packet size=4096;integrated security=SSPI;data
source=F16;persist security info=True;initial catalog=Distributor" /!

!/appSettings!

4. In your code, you can retrieve the connection string defined in your Web.config file using:
Dim connStr As String = _
ConfigurationSettings.AppSettings("Distributor")
Dim Conn As New SqlConnection(connStr)

5. Next, use the aspnet_setreg.exe utility to add the username and password of the user account that your ASP.NET application will impersonate, into the registry:
C:\>aspnet_setreg -k:Software\ASPNetApp\Identity -u:ASPNETUSER -p:secret

6. When you do that, Windows will print a long message to the screen. The text of the message is shown below. In particular, look for two lines denoted in bold. You will need to save the two lines in a text file.
Please edit your configuration to contain the following:
userName="registry:HKLM\Software\ASPNetApp\Identity\ASPNET_SETREG,userName"
password="registry:HKLM\Software\ASPNetApp\Identity\ASPNET_SETREG,password"
The DACL on the registry key grants Full Control to System, Administrators, and Creator Owner.
If you have encrypted credentials for the configuration section, or a connection string for the
!sessionState/! configuration section, ensure that the process identity has Read access to the
registry key. Furthermore, if you have configured IIS to access content on a UNC share, the account used to
access the share will need Read access to the registry key.
Regedt32.exe may be used to view/modify registry key permissions.
You may rename the registry subkey and registry value in order to prevent discovery.

7. Locate the Machine.config file at: C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322\CONFIG and modify the !identity! element in Machine.config file to:
!identity impersonate="true"
userName="registry:HKLM\Software\ASPNetApp\Identity\ASPNET_SETREG,userName"
password="registry:HKLM\Software\ASPNetApp\Identity\ASPNET_SETREG,password"
/!

8. Launch the registry editor and navigate to My Computer/HKEY_LOCAL_MACHINE/SOFTWARE/ASPNetApp/Identity/ASPNET_SETREG (use regedt32)

9. Right-click on the ASPNET_SETREG registry key and select Permissions. Add the user account ASPNET and set it to Read permission

10. Give the user account ASPNETUSER FULL CONTROL access rights to the “Temporary ASP.NET Files” folder located in C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322.

11. That’s it! Your application will now run under the impersonation of ASPNETUSER. And the credentials of the user can be securely retrieved from the registry.

7. Web.config before deployment
a. Turn trace off:
!trace enabled="false" requestLimit="10" pageOutput="false" traceMode="SortByTime" localOnly="true" /!

b. Turn debug off:
!compilation defaultLanguage="vb" debug="false" /!

c. Set customErrors to RemoteOnly:
!customErrors mode="RemoteOnly" /!
The mode attribute has three possible values:
>"On" always display custom (friendly) messages
>"Off" always display detailed ASP.NET error information.
>"RemoteOnly" displays custom (friendly) messages only to users not running on the local Web server. This setting is recommended for security purposes, so that you do not display application detail information to remote clients.

d. Remove Solution and Project files from your deployment server.


Reference: http://www.devx.com/security/Article/20898

SSRS: Remove or Order Exports

To change order of export to options in SRS we need to change RsReportServer.config file. Change the order and they will get reflected in the UI.
Alternative set :

Visible="false"

to hide the export options.

note: This will apply to ALL reports on the server

ASP .NET: Assigning Data to DataSet table

WebConfig

connectionStrings
add name="HRDWConnString" connectionString="Data Source=.;Initial Catalog='HR DW';Integrated Security=True" providerName="System.Data.SqlClient"/
/connectionStrings

Code

//Connect to DB
string connStr = ConfigurationManager.ConnectionStrings["YOURDBCONNECTION IN WEB CONFIG"].ConnectionString;
SqlConnection conn = new SqlConnection(connStr);
SqlCommand comm = conn.CreateCommand();
comm.CommandText = @"SELECT
--[DisplayName]
[FileName]
,[ParameterName]
,[DefaultValue]
FROM [dbo].[vwLookupReportParameterDefaults]";
try
{
conn.Open();
SqlDataAdapter ad = new SqlDataAdapter(comm.CommandText.ToString(),conn);
ad.Fill(dataset, "Parameters");
Cache["dataset"] = ds;

//SqlDataReader dr = comm.ExecuteReader();
//while (dr.Read())
//{
//ReportParameters = new ListItem(dr[2].ToString(), dr[3].ToString());
//}
}
catch (Exception ex)
{
Response.Redirect("LoginError.aspx");
}
finally
{
conn.Dispose();
}

DataSet d = new DataSet();
d = (DataSet)Convert.ChangeType(Cache["dataset"], typeof(DataSet));
dt = d.Tables[0];
for (int i =0; i < dt.Rows.Count; i++) {
if (report == dt.Rows[i]["FileName"].ToString())
{
ReportParameter param = new ReportParameter(dt.Rows[i]["ParameterName"].ToString(), dt.Rows[i]["DefaultValue"].ToString());
ReportViewer1.ServerReport.SetParameters(new ReportParameter[] { param });
}
}

ASP .NET: Caching values and Casting

Cache["dataset"] = Dataset; //Your Object

newDataset = (DataSet)Convert.ChangeType(Cache["dataset"], typeof(DataSet)); //Cast cached to newDataset

Details:
http://aspnet.4guysfromrolla.com/articles/022802-1.aspx

SSAS: Tips

http://www.obs3.com/as_tips.shtml

SSAS: Current Month in Cube

A named set which resolves the "current month" is very useful when building reports because the report can reference the named set rather than an individual month, which would need to be continually changed. Here is a piece of MDX code which returns the last month in the database which contains data.
Current Month = Tail( Filter( [Time].[Month].Members, Not IsEmpty([Time].CurrentMember)), 1)
Note: If you want to set the time dimension's "default member" to use the "current month" you will need to include .item(0) at the end of the statement to convert the set to an individual member.
The tips provided here have no implied warranty. They are provided as a free service and are informational in nature.

ASP .NET: User Authentication not going through to reports

Add:

authentication mode="Windows"/
identity impersonate="true"/

to web.config in System.Web

ASP .net: reportViewer Operation is not valid due to the current state of the object

While you are in a drillthrough report, the LocalReport object represents the drillthrough report - the value of the report path, parameters, etc. Conceptually, you can't change this report, it was defined by the parent report. If you could, it would leave the back button enabled on the toolbar, which doesn't really make sense. The correct thing to do is first call ReportViewer.Reset(). This will reset the report stacks and allow you to set a new definition.

ASP .NET: Update framework 2.0 to 3.5

Changes to the Default Web.config File in Visual Studio 2008As discussed in An Overview of ASP.NET 3.5 and Visual Studio 2008, Visual Studio 2008 can be used to create ASP.NET version 2.0, version 3.0, or version 3.5 applications. To specify the version being targeted, right-click on the website project in the Solution Explorer and select Property Pages from the context menu. From the Property Pages, select the Build option in the left column. You can then choose the version to target from the drop-down list on the right.

Visual Studio 2008 automatically updates the application's Web.config file based on the version being targeted.
Similarly, when you open an existing ASP.NET 2.0 application in Visual Studio 2008, you are prompted as to whether you want to upgrade the website to ASP.NET version 3.5.

Ref: http://www.4guysfromrolla.com/articles/121207-1.aspx