Archive

Archive for the ‘Uncategorized’ Category

High-speed Hierarchies

 

Remember collation is VERY important

[SETSTRING] [nvarchar](255)
COLLATE Latin1_General_BIN NULL

 

Theory here:

 

http://www.yafla.com/papers/sqlhierarchies/sqlhierarchies.htm


 

Advertisements
Categories: Uncategorized

Consuming Web Services over HTTPS (SSL)

When Webservices are used, a common concern is security: SOAP messages are transferred in plain text over the network, so anyone with a sniffer could intercept the SOAP message and read it. In my opinion this could happen also to binary data, but probably it requires a little bit more hacker skills. So a solution is to use HTTPS (SSL) instead of HTTP, so the communication is encrypted. To accomplish this, you need to get and install a certificate (issued by a Certificate Authority) on your webserver. In a production environment you would buy a certificate from Verisign or another well known CA, or you would install your own CA, which is a component of Windows Server. If you only want to play with HTTPS, SSL and certificates or your project is in the development phase, you can also generate a test certificate using the MakeCert.exe tool (included in the .NET Framework SDK). After that you have to add this certificate to a website in IIS, and set a port which HTTPS should use.

 

When you browse to a HTTPS site, you probably get a dialog window asking you if you want to trust the certificate provided by the webserver. So the responsibility of accepting the certificate is handled by the user. Let’s get back to the webservice scenario, if you want to invoke a webservice located on a webserver which uses SSL and HTTPS there is a problem. When you make the call from code, there is no dialog window popping up, and asking if you trust the certificate (luckily because this would be pretty ugly in server-side scenarios); probably you’ll get following exception:

An unhandled exception of type ‘System.Net.WebException’ occurred in system.dll

 

Additional information: The underlying connection was closed: Could not establish trust relationship with remote server.

 

But there is a solution for this problem, you can solve this in your code by creating your own CertificatePolicy class (which implements the ICertificatePolicy interface). In this class you will have to write your own CheckValidationResult function that has to return true or false, like you would press yes or no in the dialog window. For development purposes I’ve created the following class which accepts all certificates, so you won’t get the nasty WebException anymore:

public class TrustAllCertificatePolicy : System.Net.ICertificatePolicy

{

public TrustAllCertificatePolicy()

{}

 

public bool CheckValidationResult(ServicePoint sp,

X509Certificate cert,WebRequest req, int problem)

{

return true;

}

}

 

As you can see the CheckValidationResult function always returns true, so all certificates will be trusted. If you want to make this class a little bit more secure, you can add additional checks using the X509Certificate parameter for example. To use this CertificatePolicy, you’ll have to tell the ServicePointManager to use it:

System.Net.ServicePointManager.CertificatePolicy = new TrustAllCertificatePolicy();

This must be done (one time during the application life cycle) before making the call to your webservice.

Categories: Uncategorized

Consuming Web Services with SSL and HTTP Basic

 

Consuming WebServices with SSL And HTTP BASIC Authentication

by Peter A. Bromberg, Ph.D.

 

    Peter Bromberg

 

I have to admit that I thought this one was going to be easy, and it ended up taking several frustrating hours to get it all “right”, mostly due to a lack of quality documentation on the subject, and secondarily because of some seemingly logical assumptions that any developer would make, which turned out to be false, as we will soon see.

Accepting Server SSL Certificates Gracefully

 

First, let’s deal with the SSL issue. This is not particularly odd, many vendors run their webservices under Apache with modSSL while requiring HTTP BASIC authentication “under the hood”, and the .NET Framework does not provide an easy – to – use, out of the box way to accept Server Certificates on a normal WebService proxy method call.

Ads by Google

Vacation Certificates     Free guide filled with ideas on growing your business.     www.SunrisePremiums.net

Ssl Certificate Authority     Get the strongest SSL from VeriSign Protect Important Data – Learn More     www.VeriSign.com

SSL Certificates – thawte     Choose from a complete range of certificates with the strongest SSL     www.thawte.com

SOAP Test Client     Send and Recieve SOAP Messages Test your Web Services Today!     www.IntegrationCentral.com

SSL Certificates $27.95     Simple low priced SSL 24/7 Live Customer Support     www.skysoftinc.com

 

The trick to the SSL issue is that we must override the default System.Net.ServicePointManager’s CertificatePolicy, which underlies every SOAP call, with a custom class derived from ICertificatePolicy. Essentially what this class will do when it’s revised boolean CheckValidationResult is automatically called, is return true (accept any Server Certificate) regardless of whether or what errors may exist. The class I present here (which can be found on MSDN) also allows to return a problem message based on a standardized enumeration:

 

//SSL Certificate code

public enum CertificateProblem : long

{

CertEXPIRED = 0x800B0101,

CertVALIDITYPERIODNESTING = 0x800B0102,

CertROLE = 0x800B0103,

CertPATHLENCONST = 0x800B0104,

CertCRITICAL = 0x800B0105,

CertPURPOSE = 0x800B0106,

CertISSUERCHAINING = 0x800B0107,

CertMALFORMED = 0x800B0108,

CertUNTRUSTEDROOT = 0x800B0109,

CertCHAINING = 0x800B010A,

CertREVOKED = 0x800B010C,

CertUNTRUSTEDTESTROOT = 0x800B010D,

CertREVOCATION_FAILURE = 0x800B010E,

CertCN_NO_MATCH = 0x800B010F,

CertWRONG_USAGE = 0x800B0110,

CertUNTRUSTEDCA = 0x800B0112

}

 

public class TrustAllCertificatePolicy : ICertificatePolicy

{

// Default policy for certificate validation.

public static bool DefaultValidate = false; // set to true for testing

 

public bool CheckValidationResult(ServicePoint sp, X509Certificate cert,

WebRequest request, int problem)

{

bool ValidationResult=false;

Console.WriteLine(“Certificate Problem with accessing ” +

request.RequestUri);

// Console.Write(“Problem code 0x{0:X8},”,(int)problem);

//Syslog send-

Sender.Send( GetProblemMessage((CertificateProblem)problem));

 

ValidationResult = DefaultValidate;

return ValidationResult;

}

 

private String GetProblemMessage(CertificateProblem Problem)

{

String ProblemMessage = “”;

CertificateProblem problemList = new CertificateProblem();

String ProblemCodeName = Enum.GetName(problemList.GetType(),Problem);

if(ProblemCodeName != null)

ProblemMessage = ProblemMessage + “-Certificateproblem:” +

ProblemCodeName;

else

ProblemMessage = “Unknown Certificate Problem”;

return ProblemMessage;

}

}

 

You can place this class in the same namespace as your WebService proxy WSDL – generated code, and that makes it easier. To use it, you would use the following sample code:

 

MyWebServiceProxy myWebServiceProxy = new MyWebServiceProxy();

// set a default CertificatePolicy that accepts ALL Server certificates�.

System.Net.ServicePointManager.CertificatePolicy = new TrustAllCertificatePolicy();

myWebServiceProxy.MyMethod(myparam1,myparam2) ;

 

Everything else with the WebService call over SSL is a no-brainer. Provided that the Url Property has “https://” at the beginning, it will automatically negotiate transmission over the default port of 443.

Negotiating HTTP BASIC “true” Pre-Authentication

 

That takes care of the SSL issue. Now let’s deal with the HTTP BASIC issue:

 

The “Gotcha” for the average developer is that the WebService Proxy class has UserName, Password, etc. properties and this leads us to believe we can simply set these properties and get “Authentication”. It also sports a PreAuthenticate property that fills in the puzzle. Unfortunately, it’s just not that simple.

 

HTTP Webservers support multiple authentication options. On Windows, IIS supports Basic, NTLM, Digest, and Negotiate (a.k.a. Integrated Windows Authentication). Of these, Basic & Kerberos both support “PreAuthentication”.

 

However, “PreAuthentication” is often misunderstood to mean “Send the credentials on the very first request”. That is not true. For Preauthenticate to work, the following must occur:

 

1) You make a webrequest to a server (a SOAP Call). You may or may not have specified credentials on the first request.

 

2) The server, which is configured for BASIC auth, wants to authenticate the request you are making, so it replies with a 401.

 

3) The client does the authentication handshake with the server, and sends your credential (a Base64 encoded string consisting basically of “username:password”).

 

4) If “Basic” was chosen as the authentication scheme by the client, then Pre-Authentication is enabled for this request. So, every subsequent request from now on to the same Uri-Prefix will result in the credentials being sent without any challenge required.

 

The main point to note here is that there must be at least one request before the client can expect pre-authentication. Credentials are not automatically sent on the first request just because PreAuthenticate is set to true. I figure that if this was a wakeup call for me (which it was) , then I am probably far from alone!

 

To force credentials to be sent on the first request (and thus avoid the first authenticate handshake), you can set credentials manually on the request:

 

MyWebServiceProxy myWebServiceProxy = new MyWebServiceProxy();

myWebServiceProxy.Url =this.endpointUrl; //items from your appConfig

NetworkCredential netCredential = new NetworkCredential(this.endpointUserName,this.endpointPassword );

Uri uri = new Uri(this.endpointUrl);

ICredentials credentials = netCredential.GetCredential(uri, “Basic”);

myWebServiceProxy.Credentials = credentials;

// Be sure to set PreAuthenticate to true or else authentication will not be sent.

mMyWebServiceProxy.PreAuthenticate = true;

 

What this does is set the authorization header on the request, and send it on the first request itself. If the credentials are correct, the first handshake will not occur. However, remember that unless you have set Preauthenticate to true, you have to use this code to set the authorization header on every request. This is because you have bypassed the authentication handshake, and the SOAP HttpWebRequest has no knowledge that the server requires authentication. So it won’t do pre-authentication by itself anymore.

 

The recommended way to get preauthentication is to set the credentials on the request, set PreAuthenticate=true, and send the request. Once the first authentication handshake happens, and credentials are accepted by the server, preauthentication will happen behind the scenes for every request to the same Uri-Prefix.

 

Now here is a final block of sample code that combines both the HTTP BASIC with PreAuthenticate along with the SSL “Accept all certificates” code that (at least for me) got me onto first base:

 

MyWebServiceProxy myWebServiceProxy = new MyWebServiceProxy();

// set a default CertificatePolicy that accepts ALL Server certificates

System.Net.ServicePointManager.CertificatePolicy =

new TrustAllCertificatePolicy();

 

myWebServiceProxy.Url =this.endpointUrl; //items from your appConfig

NetworkCredential netCredential = new NetworkCredential(this.endpointUserName,this.endpointPassword );

Uri uri = new Uri(this.endpointUrl);

ICredentials credentials = netCredential.GetCredential(uri, “Basic”);

myWebServiceProxy.Credentials = credentials;

// Be sure to set PreAuthenticate to true or else authentication will not be sent.

myWebServiceProxy.PreAuthenticate = true;

myWebServiceProxy.MyMethod(myparam1,myparam2) ;

 

Good Luck!

Categories: Uncategorized

Concatenate files to a single file

 


copy *.txt, myfile.txt

Categories: Uncategorized

How to enable CLR Stored Procedures

Here are two ways to enable CLR Stored Procedures

 

1. Explore “SQL Server 2005/Configuration Tools/Surface Area Configuration” in your Start menu.

2. Select “Surface Area Configuration for Features”

3. You will find “CLR Integration” option, activate it and save.

 

sp_configure ‘clr enabled’, 1

go

reconfigure

go

Categories: Uncategorized

Code For CLR Stored Procedure Dynamic Table as a Return

using
System;

using
System.Data;

using
System.Data.SqlClient;

using
System.Data.SqlTypes;

using
Microsoft.SqlServer.Server;

using
System.Collections.Generic;

 

 

public
partial
class
StoredProcedures

{


public
readonly
static
string[] months = { “Oct”, “Nov”, “Dec”, “Jan”, “Feb”, “Mar”, “Apr”, “May”, “Jun”, “Jul”, “Aug”, “Sep” };

 

[Microsoft.SqlServer.Server.SqlProcedure]


public
static
void
TA_GetEstimating(int
Contract_ID, string
type, string
fy)

{

 


using (SqlConnection
connection = new
SqlConnection(“context connection=true”))

{


connection.Open();


SqlCommand
command = null; // new SqlCommand(“SELECT * FROM TA_Period where Period_ID in(” + strPeriods + “)”, connection);


SqlDataReader
reader = null;// command.ExecuteReader();

 


//Setup the table to return


SqlMetaData[] parms = GetColumns(type);


// Create the record and specify the metadata for the columns.


SqlDataRecord
record = new
SqlDataRecord(parms);

 

#region Get the people


command = new
SqlCommand(“SELECT Contract_ID, TA_Person_Contract.Person_ID, Company_Name, Last_Name + ‘, ‘ + First_Name AS Name, Authorized_Hours, Estimated_Hours, “ +


“(SELECT MAX(TA_Contract_Task_Labor.Rate) AS Expr1 FROM TA_Contract_Task_Labor_Person INNER JOIN “ +


“TA_Contract_Task_Labor ON TA_Contract_Task_Labor_Person.Contract_ID = TA_Contract_Task_Labor.Contract_ID AND “ +


“TA_Contract_Task_Labor_Person.Task_ID = TA_Contract_Task_Labor.Task_ID AND “ +


“TA_Contract_Task_Labor_Person.Labor_ID = TA_Contract_Task_Labor.Labor_ID “ +


“WHERE (TA_Contract_Task_Labor_Person.Contract_ID = @Contract_ID) AND (TA_Contract_Task_Labor_Person.Person_ID = TA_Person_Contract.Person_ID)) as Rate “ +


“FROM TA_Person_Contract INNER JOIN TA_Person ON TA_Person_Contract.Person_ID = TA_Person.Person_ID WHERE (Contract_ID = @Contract_ID) ORDER BY Name”, connection);


SqlParameter
p = new
SqlParameter(“@Contract_ID”, SqlDbType.Int);


p.Value = Contract_ID;


command.Parameters.Add(p);


reader = command.ExecuteReader();

 


List<Person> listPeople = new
List<Person>();


using (reader)

{


while (reader.Read())

{


Person
person = new
Person();


person.PersonID = (int)reader[“Person_ID”];


person.Company = reader[“Company_Name”].ToString();


person.Name = reader[“Name”].ToString();


person.EstimatedHours = (decimal)reader[“Estimated_Hours”];


person.Rate = (decimal)reader[“Rate”];


listPeople.Add(person);

}

}

#endregion

 


// Mark the begining of the result-set.


SqlContext.Pipe.SendResultsStart(record);

 

 


List<string> messages = new
List<string>();


foreach (Person
person
in
listPeople)

{


//throw new Exception(“Got Here”);


record.SetInt32(0, person.PersonID);

 


SqlString
s = new
SqlString(person.Name);


record.SetSqlString(1, s);

 


s = new
SqlString(person.Company);


record.SetSqlString(2, s);

 


for (int
i = 0; i < 12; i++)

{


int
index = 4 * i;


string
periods = GetPeriods(i + 1, connection, type, fy);


messages.Add(“Periods for “ + i.ToString() + ” – “ + periods);

 


//Actual Hours


record.SetDecimal(index + 3, GetMonthAH(connection, Contract_ID, person.PersonID, periods));


//Actual Cost


record.SetDecimal(index + 4, GetMonthAC(connection, Contract_ID, person.PersonID, periods));

 


//Get the estimated hours


decimal
eh = GetMonthEH(connection, Contract_ID, person.PersonID, person.EstimatedHours, periods);


//Estimated hours


record.SetDecimal(index + 5, eh);


//Estimated Cost EH * (max rate)


record.SetDecimal(index + 6, eh * person.Rate);

}


// Send the row back to the client.


SqlContext.Pipe.SendResultsRow(record);

 

}


// Mark the end of the result-set.


SqlContext.Pipe.SendResultsEnd();


foreach (string
message
in
messages)


SqlContext.Pipe.Send(message);

}

}


private
static
string
GetPeriods(int
monthOrder, SqlConnection
connection, string
type, string
fy)

{


string
period = “”;


switch (type)

{


case
“Month”:


default:


SqlCommand
command = new
SqlCommand(“select period_ID from TA_Period where fy = @fy and MonthOrder = @Month”, connection);

 


SqlParameter
p = new
SqlParameter(“@fy”, SqlDbType.VarChar, 2);


p.Value = fy;


command.Parameters.Add(p);

 


p = new
SqlParameter(“@Month”, SqlDbType.Int);


p.Value = monthOrder;


command.Parameters.Add(p);

 

 


SqlDataReader
reader = command.ExecuteReader();


while (reader.Read())

{


if (period != “”)


period += “, “;


period += reader[“Period_ID”].ToString();

}


reader.Close();


break;

}


return
period;

}

 


private
static
SqlMetaData[] GetColumns(string
type)

{


SqlMetaData[] parms = null;

 


switch (type)

{


case
“Month”:


default:

#region Month columns


parms = new
SqlMetaData[51];

 


parms[0] = new
SqlMetaData(“Person_ID”, SqlDbType.Int);


parms[1] = new
SqlMetaData(“Name”, SqlDbType.VarChar, 100);


parms[2] = new
SqlMetaData(“Company”, SqlDbType.VarChar, 100);

 


for (int
i = 0; i < 12; i++ )

{


int
index = 4 * i;


parms[index + 3 ] = new
SqlMetaData(months[i] + “AH”, SqlDbType.Decimal, byte.Parse(“20”), byte.Parse(“2”));


parms[index + 4] = new
SqlMetaData(months[i] + “AC”, SqlDbType.Decimal, byte.Parse(“20”), byte.Parse(“2”));


parms[index + 5] = new
SqlMetaData(months[i] + “EH”, SqlDbType.Decimal, byte.Parse(“20”), byte.Parse(“2”));


parms[index + 6] = new
SqlMetaData(months[i] + “EC”, SqlDbType.Decimal, byte.Parse(“20”), byte.Parse(“2”));

}

 

#endregion


break;


case
“Period”:


break;

}


return
parms;

}


private
static
decimal
GetMonthAH(SqlConnection
connection, int
contract, int
person, string
period)

{


decimal
hours = 0;


string
sql = “SELECT ISNULL(Sum(Hours), 0) FROM TA_TimeEntry where “ +


“Contract_ID = @Contract_ID and “ +


“Person_ID = @Person_ID and “ +


“Period_ID in(“ + period + “)”;


SqlCommand
comPeriod = new
SqlCommand(sql, connection);


comPeriod.Parameters.Add(new
SqlParameter(“@Person_ID”, SqlDbType.Int));


comPeriod.Parameters[0].Value = person;


comPeriod.Parameters.Add(new
SqlParameter(“@Contract_ID”, SqlDbType.Int));


comPeriod.Parameters[1].Value = contract;


hours = (Decimal)comPeriod.ExecuteScalar();

 


return
hours;

}


private
static
decimal
GetMonthAC(SqlConnection
connection, int
contract, int
person, string
period)

{


decimal
hours = 0;

 


string
sql = “SELECT ISNULL(Sum(TA_Contract_Task_Labor.Rate * TA_TimeEntry.Hours), 0) “ +


“FROM TA_Contract_Task_Labor INNER JOIN TA_TimeEntry ON TA_Contract_Task_Labor.Contract_ID = TA_TimeEntry.Contract_ID AND TA_Contract_Task_Labor.Labor_ID = TA_TimeEntry.Labor_ID AND TA_Contract_Task_Labor.Task_ID = TA_TimeEntry.Task_ID “ +


“WHERE “ +


“TA_TimeEntry.Contract_ID = @Contract_ID and “ +


“Person_ID = @Person_ID and “ +


“Period_ID in(“ + period + “)”;


SqlCommand
comPeriod = new
SqlCommand(sql, connection);


comPeriod.Parameters.Add(new
SqlParameter(“@Person_ID”, SqlDbType.Int));


comPeriod.Parameters[0].Value = person;


comPeriod.Parameters.Add(new
SqlParameter(“@Contract_ID”, SqlDbType.Int));


comPeriod.Parameters[1].Value = contract;


hours = (Decimal)comPeriod.ExecuteScalar();

 


return
hours;

}


private
static
decimal
GetMonthEH(SqlConnection
connection, int
contract, int
person, decimal
estHours, string
period)

{


decimal
hours = 0;


string
sql = “SELECT ISNULL((SELECT EstHours FROM TA_Person_Contract_Period AS pcp WHERE Person_ID = @Person_ID AND Contract_ID = TA_Contract_Period.Contract_ID AND Period_ID = TA_Contract_Period.Period_ID), @EstHours) AS EstHours “ +


“FROM TA_Contract_Period INNER JOIN TA_Period ON TA_Contract_Period.Period_ID = TA_Period.Period_ID WHERE “ +


“(TA_Contract_Period.Contract_ID = @Contract_ID) AND “ +


“(TA_Contract_Period.Period_ID IN (“ + period + “))”;


SqlCommand
comPeriod = new
SqlCommand(sql, connection);


comPeriod.Parameters.Add(new
SqlParameter(“@Person_ID”, SqlDbType.Int));


comPeriod.Parameters[0].Value = person;


comPeriod.Parameters.Add(new
SqlParameter(“@Contract_ID”, SqlDbType.Int));


comPeriod.Parameters[1].Value = contract;


comPeriod.Parameters.Add(new
SqlParameter(“@EstHours”, SqlDbType.Decimal));


comPeriod.Parameters[2].Value = estHours;


SqlDataReader
reader = comPeriod.ExecuteReader();

 


while (reader.Read())

{


hours += (decimal)reader[“EstHours”];

}


reader.Close();


return
hours;

}


private
static
decimal
GetMonthEC(SqlConnection
connection, int
contract, int
person, decimal
estHours, string
period, decimal
rate)

{


decimal
hours = 0;


string
sql = “SELECT ISNULL((SELECT EstHours FROM TA_Person_Contract_Period AS pcp WHERE Person_ID = @Person_ID AND Contract_ID = TA_Contract_Period.Contract_ID AND Period_ID = TA_Contract_Period.Period_ID), @EstHours) AS EstHours “ +


“FROM TA_Contract_Period INNER JOIN TA_Period ON TA_Contract_Period.Period_ID = TA_Period.Period_ID WHERE “ +


“(TA_Contract_Period.Contract_ID = @Contract_ID) AND “ +


“(TA_Contract_Period.Period_ID IN (“ + period + “))”;


SqlCommand
comPeriod = new
SqlCommand(sql, connection);


comPeriod.Parameters.Add(new
SqlParameter(“@Person_ID”, SqlDbType.Int));


comPeriod.Parameters[0].Value = person;


comPeriod.Parameters.Add(new
SqlParameter(“@Contract_ID”, SqlDbType.Int));


comPeriod.Parameters[1].Value = contract;


comPeriod.Parameters.Add(new
SqlParameter(“@EstHours”, SqlDbType.Decimal));


comPeriod.Parameters[2].Value = estHours;


SqlDataReader
reader = comPeriod.ExecuteReader();

 


while (reader.Read())

{


hours += (decimal)reader[“EstHours”];

}


reader.Close();


return
hours;

}

};

 

internal
class
Person

{


public
int
PersonID;


public
string
Company;


public
string
Name;


public
decimal
EstimatedHours;


public
decimal
Rate;

}

Categories: Uncategorized