Giving IIS 7 AppPoolIdentity account access to SQL Server

IIS 7 has a nifty new feature whereby you can set the identity of the application pool to ApplicationPoolIdentity. It then creates a virtual user with the same name as the name of the application pool, i.e. if you named your application pool Bozo, it will create a virtual user called Bozo, to which you can assign rights.

To allow the virtual user access to SQL Server, do the following:

• In SQL Server Management Studio, open the Security folder, right-click the Logins folder, and click New Login...
• Enter IIS AppPool\{apppoolname}, replacing {apppoolname} with the name of your application pool - DO NOT USE THE SEARCH FUNCTIONALITY
• Assign the necessary database level privledges and click Ok

This has been tested on SQL Server 2005, I would imagine it works in SQL2008 too.

Source: http://efreedom.com/Question/1-1933134/Add-IIS-AppPool-Identities-SQL-Server-Logons

Forced breaks - a good thing

All of us that work with computers are guilty of one thing: we don't take enough breaks to give our eyes a break, leading to fatigue at the end of the day. Ubuntu 10.04 has functionality built-in to help you manage not working for too long at a time. You can set the duration of the work interval as well as of the break, and you can postpone breaks. I'm definitely going to use this, and also look for a similar app for Windows.

You can find the Typing break configuration under System/Preferences/Keyboard on the Typing Break tab

Here is a screenshot of the functionality.

To umlaut or not

A recent project required the bulk insert of data into a table in SQL Server. The file contained data with umlaut (ü) and accent (É) characters.

The vanilla BULK INSERT did not work very well with this, changing it to other characters. After quite a bit of googling, I found the solution was to add

WITH (codepage = '1252')

to the BULK INSERT statement. The default codepage on most Windows computers appear to be 437. The lowercase umlaut's code is 252 in the ANSI table. Character 252 in codepage 437 is 'n', so that's what you'll end up with in your data rather than the umlaut.

Ubuntu - speakers not muted when inserting earphone jack - SOLUTION

I recently installed ubuntu 10.04 on a laptop, and found that the built-in speakers are not muted when inserting the earphone jack. After I googled a bit, I found the solution:

run alsamixer
use the left and right arrow keys to move between items, look for the Headphone Jack Sense item, it should say [Off] at the end, and MM in the box.
Press m to change it to 00.

From now on the built-in speakers should be muted when inserting the earphone jack.

Webservice Studio 2.0.2 patch

Recently a colleague needed to connect to a webservice using Client Certificate authentication. Webservice Studio 2.0.2 does not cater for this, so I did some digging and managed to get it working.

I tried contacting the coordinator listed on http://webservicestudio.codeplex.com/team/view but got no response. I do however want to give it back to the community, so here is the patch you need to apply to 2.0.2.

--- Wsdl.cs Thu Jan 15 11:13:00 1970
+++ Wsdl.cs Thu Jan 15 11:13:00 1970
@@ -19,6 +19,8 @@
     using System.Xml.Schema;
     using System.Xml.Serialization;
 
+    using System.Security.Cryptography.X509Certificates;
+
     internal class Wsdl
     {
         private bool cancelled = false;
@@ -292,6 +294,17 @@
             {
                 protocol.Credentials = CredentialCache.DefaultCredentials;
             }
+
+            if (this.WsdlProperties.UseClientCert)
+            {
+                X509Certificate2 cert = GetClientCert();
+
+                if (cert != null)
+                {
+                    protocol.ClientCertificates.Add(cert);
+                }
+            }
+
             if ((this.WsdlProperties.ProxyServer != null) && (this.WsdlProperties.ProxyServer.Length != 0))
             {
                 IWebProxy proxy = null;
@@ -300,6 +313,21 @@
                 protocol.Proxy = proxy;
             }
             return protocol;
+        }
+
+        private X509Certificate2 GetClientCert()
+        {
+            X509Store certStore = new X509Store(StoreLocation.CurrentUser);
+            certStore.Open(OpenFlags.OpenExistingOnly | OpenFlags.ReadOnly);
+            X509Certificate2Collection selectedCerts = X509Certificate2UI.SelectFromCollection(certStore.Certificates, "Select a certificate", "Please select the client certificate you would like to use", X509SelectionFlag.SingleSelection);
+            
+            certStore.Close();
+
+            if (selectedCerts.Count > 0)
+            {
+                return selectedCerts[0];
+            }
+            return null;
         }
 
         private static XmlSchema CreateFakeSoapEncodingSchema(string ns, string name)
--- WsdlProperties.cs Thu Jan 15 11:13:00 1970
+++ WsdlProperties.cs Thu Jan 15 11:13:00 1970
@@ -18,6 +18,14 @@
         private string proxyPassword;
         private string proxyUserName;
         private string userName;
+        private bool useClientCert;
+
+        [XmlAttribute]
+        public bool UseClientCert
+        {
+            get { return useClientCert; }
+            set { useClientCert = value; }
+        }
 
         public string[] GetProxyBaseTypeList()
         {

Powershell takes a dive

Yesterday I was happily running some PowerShell scripts as part of our build process, when suddenly PowerShell took a dive for the worse.

All attempts to run PowerShell scripts returned the following error:

AuthorizationManager check failed

After a lot of fiddling and checking the Group Policy settings for PowerShell, I decided to reboot. Guess what, problem solved!

Let that be a reminder of MS std support action #1: reboot

Locked out of SQL Server

Our active directory was recently migrated to a new domain, and because of this, I could not log in to SQL Server with my new user account. Problem is, I also forgot my sa password, so I could not add my new user account.

This is where single user mode comes in. You restart SQL Server in single-user mode, add the new user or reset the sa password, and then restart it in normal mode.

What do you need to do to start it in single user mode?

Open SQL Server Configuration Manager and click SQL Server Services
Right-click on the instance and click Properties.
Click the Advanced tab, and type the startup parameters into the Startup Parameters box (the parameters are semi-colon delimited, so add -m; to the front.
Click OK
Restart the database engine.

Remember to remove the single user startup option and restart the database after resetting the password or adding the user!

Reference: MSDN

SQL: Back to basics

Today's lesson might be common knowledge for you, but for me it was a good back to basics again. It concerns the calling of scalar functions from the WHERE clause of a SELECT statement.

One could expect the following statement to execute the function only once, but, then, you'd be wrong.

select * from sys.objects
where dbo.myfunc(1) > 1

SQL server executes the function once for every row in sys.objects, even though it is called with a constant parameter and will never return a different result. Typically this is something you would not pick up in development with a small dataset, but performance will decrease dramatically as the dataset size increases.

So, the right way to write the above SQL statement would be something like

declare @num int
set @num=dbo.myfunc(1)
select * from sys.objects
where @num > 1

Be aware, be very aware!

Rounding those corners

A client recently requested a web-based wallboard/dashboard for their one callcentre, and I wanted to give it a nice smooth look, this is where rounded corners come into play.

I had a look at various solutions available, some using images and some scripting. In the end I settled on a non-image Javascript solution called Nifty Corners.

It really is easy to add to your existing design.

<script type="text/javascript" src="niftycube.js"></script>
<script type="text/javascript">
window.onload=function(){
Nifty("div#box","big");
}
</script>

I had some issues with alignment and sizing due to the CSS layout with floating divs used, but those issues have nothing to do with Nifty Corners itself.

Here is a part of a screenshot of the final product. I think most people will agree it looks better than the normal sharp corners if you don't apply styling.



Nifty Corners Cube are released under the GNU GPL licence, so you can use it freely in your sites.

Browser resizing extension for Chrome

I found a nice extension for Chrome to resize the browser window to test your web app in different resolutions, and thought I'll share it.

https://chrome.google.com/extensions/detail/idhfcdbheobinplaamokffboaccidbal

Floating div solved

In a previous post I had some issues with floating divs. I asked a CSS guru, and all that I had to do was set the height attribute on the floating divs to get it behaving as expected.

Thanks darkelf!

Have I gone mad?

I'm busy putting together a web-based wallboard for a callcentre, and suddenly my prototype started going crazy. I'm doing all the layout with CSS, and it worked very well, until suddenly, it went crazy.

Before:


After:


As you can see, the floating divs are going horribly wrong. What did I change? The only change was to add a font-family attribute to the body class in the stylesheet, like this:

body
{
    font-family: Verdana, Arial;
}

Can any CSS or HTML guru explain to me why this happens? If I take away Verdana and leave Arial, it works in Chrome, I have to remove the whole font-family line for it to work in IE7.

Awaiting your comments...

Create new event log source

Normally ASP.Net does not have the required privileges to create an event log source, so you have to create one manually, or execute the creation code with the required privileges.

To sort out this little problem, there is a command line utility included in Windows called eventcreate. I've NEVER heard of this before, but apparently it has existed for a long time. It actually creates an event, but will create the specified event log source if called with the correct priviliges, like the Administrator account.

eventcreate /SO "MyApp" /D "MyMessage" /L Application /T ERROR /ID 999

SUCCESS: A 'ERROR' type event is created in the 'MyApp' log/source.