Paulo Morgado [EN]

Hurrah! MVP for the seventh consecutive year!

Paulo Morgado — Thu, 02 Jul 2009 00:31:46 GMT

After all these years, it's still an honor, a privilege ... and a few stressful days.

Microsoft Tech•Ed Europe 2009 Registration Opened

Paulo Morgado — Mon, 22 Jun 2009 22:42:44 GMT

Microsoft Tech•Ed Europe 2009 Registration is now open.

There are a few novelties this year:

Due to attendee feedback, both the Developer and IT Pro editions will take place at the same time (November, 9-13).
The registration site uses Windows Live™ ID for authentication.
Registration prices are based on a tiered approach with the earlier you registration the more you will save. Register first, save the most!

Top Reasons To Attend Tech·Ed Europe 2009

EVERYONE CAN LEARN MORE ABOUT:
- MICROSOFT WINDOWS 7 - Learn about Microsoft's next-generation operating system
  
  Advancements Windows 7 brings to user productivity, security and PC management - all of which save businesses time and money.
- MICROSOFT OFFICE and new ways to maximize business productivity
  
  The Office System track will help you deploy, adopt and develop on business productivity applications.
  
  Learn about deploying Microsoft Office to your users, building pervasive Microsoft Office SharePoint solutions and managing your work with Microsoft Business Productivity Visio.
DEVELOPERS CAN LEARN MORE ABOUT:
- STREAMLINING your way to cloud applications
  
  Find out how The Azure™ Services Platform reduces the need for up-front technology purchases, and enables developers to quickly and easily create applications running in the cloud.
- SECURITY that helps cut application support costs
  
  Find out how Microsoft’s ForeFront technologies can secure your IT environment faster, and help you write more secure code to eliminate application support costs.
- TRUTH about maximizing deployments and search
  
  Find out how SharePoint can help create that central repository, maximizing deployments and search that eliminates multiple versions and misunderstandings about the source of the "truth" on any given project.
IT PROFESSIONALS CAN LEARN MORE ABOUT:
- VIRTUALISATION that helps leverage existing investments
  
  Get an in-depth understanding of today’s low-cost virtualization technologies and how they can help you make the most of your current software and hardware investments.
- HOSTING for economies of scale
  
  Learn how to take advantage of Microsoft’s hosted services to scale your IT department’s communications and collaboration offering. You’ll also lower your hardware, management and power costs.
- UNIFYING to reduce costs and help increase efficiencies
  
  Truly unified communications create a single communication infrastructure. That means reduced costs for PBX enhancements while improving productivity through unified inboxes along with rules that help workers manage interruptions.
- DEPLOYMENT - maximize your existing IT infrastructure and do more with what you've got
  
  Learn how to get maximum value out of your existing IT infrastructure.

Playing With SQL Server CLR Integration – Part IV (Deploying To SQL Server 2005)

Paulo Morgado — Sat, 13 Jun 2009 08:39:10 GMT

With all developed and tested on my laptop using SQL Server 2008, it’s time to deploy to the company’s test machine running SQL Server 2005.

The first thing I ran into when executing:

CREATE ASSEMBLY [MyAssembly]
AUTHORIZATION [dbo]
FROM '...\MyAssembly.dll'
WITH PERMISSION_SET = SAFE
GO

was:

Msg 10327, Level 14, State 1, Line 1
Assembly 'MyAssembly' references assembly 'system.xml.linq, version=3.5.0.0, culture=neutral, publickeytoken=b77a5c561934e089.',
which is not present in the current database.
SQL Server attempted to locate and automatically load the referenced assembly from the same location where referring assembly came from,
but that operation has failed (reason: 2(The system cannot find the file specified.)).
Please load the referenced assembly into the current database and retry your request.

Looks like SQL Server 2005 doesn’t know about .NET Framework 3.5. I’d just load the assemblies being used: System.Core and System.Linq.Xml:

CREATE ASSEMBLY [System.Core]
AUTHORIZATION [dbo]
FROM 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.5\System.Core.dll'
WITH PERMISSION_SET = SAFE
GO

Not so easy:

Warning: The Microsoft .Net frameworks assembly 'system.core, version=3.5.0.0, culture=neutral, publickeytoken=b77a5c561934e089, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment.
Msg 6218, Level 16, State 2, Line 1
CREATE ASSEMBLY for assembly 'System.Core' failed because assembly 'System.Core' failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database. CLR Verifier error messages if any will follow this message
[ : System.Diagnostics.Eventing.EventProvider::EtwRegister][mdToken=0x600003b][offset 0x0000003D][found Native Int][expected unmanaged pointer] Unexpected type on the stack.
[ : System.Diagnostics.Eventing.EventProvider::EncodeObject][mdToken=0x6000046][offset 0x00000000] Unmanaged pointers are not a verifiable type.
[ : System.Diagnostics.Eventing.EventProvider::WriteMessageEvent][mdToken=0x6000047][offset 0x0000003C][found ref 'System.String'] Expected numeric type on the stack.
[ : System.Diagnostics.Eventing.EventProvider::WriteEvent][mdToken=0x6000049][offset 0x0000012E] Instruction cannot be verified.
[ : System.Diagnostics.Eventing.EventProvider::WriteEvent][mdToken=0x6000049][offset 0x00000030] Instruction cannot be verified.
[ : System.Diagnostics.Eventing.EventProvider::WriteEvent][mdToken=0x600004a][offset 0x0000005F][found ref 'System.String'] Expected numeric type on the stack.
[ : System.Diagnostics.Eventing.EventProvider::WriteEvent][mdToken=0x600004b][offset 0x00000010][found unmanaged pointer][expected unmanaged pointer] Unexpected type on the stack.
[ : System.Diagnostics.Eventing.EventProvider::WriteTransferEvent][mdToken=0x600004c][offset 0x0000007D] Instruction cannot be verified.
[ : System.Diagnostics.Eventing.EventProvider::WriteTransferEvent][mdToken=0x600004c][offset 0x00000309][found Native Int][expected unmanaged pointer] Unexpected type on the stack.
[ : System.Diagnostics.Eventing.EventProvider::WriteTransferEvent][mdToken=0x600004d][offset 0x0000001B][found unmanaged pointer][expected unmanaged pointer] Unexpected type on the stack.
[ : System.Security.Cryptography.CapiNative::ImportSymmetricKey][mdToken=0x60007c2][offset 0x00000071][found address of Byte] Expected numeric type on the stac...

Ok. I’d just load it with PERMISSION_SET = UNSAFE:

CREATE ASSEMBLY [MyAssembly]
AUTHORIZATION [dbo]
FROM '...\MyAssembly.dll'
WITH PERMISSION_SET = UNSAFE
GO

Not yet:

Warning: The Microsoft .Net frameworks assembly 'system.core, version=3.5.0.0, culture=neutral, publickeytoken=b77a5c561934e089, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment.
Msg 10327, Level 14, State 1, Line 1
CREATE ASSEMBLY for assembly 'System.Core' failed because assembly 'System.Core' is not authorized for PERMISSION_SET = UNSAFE.
The assembly is authorized when either of the following is true: the database owner (DBO) has UNSAFE ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission.
If you have restored or attached this database, make sure the database owner is mapped to the correct login on this server.
If not, use sp_changedbowner to fix the problem.

Solved:

ALTER DATABASE MyDatabase SET TRUSTWORTHY ON
GO

Only than I was able to load the .NET 3.5 assemblies:

CREATE ASSEMBLY [System.Core]
AUTHORIZATION [dbo]
FROM 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.5\System.Core.dll'
WITH PERMISSION_SET = UNSAFE
GO

CREATE ASSEMBLY [System.Xml.Linq]
AUTHORIZATION [dbo]
FROM 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.5\System.Xml.Linq.dll'
WITH PERMISSION_SET = UNSAFE
GO

With all dependencies in place, I tried to load the assembly with PERMISSION_SET = SAFE with no luck:

Msg 6212, Level 16, State 1, Line 1
CREATE ASSEMBLY failed because method 'ShortPropsToXml' on type 'ShortProps'  in safe assembly 'Esi.SA.Encyclopedia' is storing to a static field.
Storing to a static field is not allowed in safe assemblies.

It had to be with PERMISSION_SET = UNSAFE.

After successfully loading the assemblies, I was finally able to create the Transact-SQL definitions for the functions (see Part I and Part II).

Now the DBAs won’t definitely let me use this, but it was fun to build it.

Playing With SQL Server CLR Integration – Part III

Paulo Morgado — Fri, 12 Jun 2009 00:11:00 GMT

You might have noticed that I used LINQ in my last Playing With SQL Server CLR Integration posts (Part I, Part II).

I couldn’t make it work with the standard Visual Studio 2008 SQL CLR project template. Changing the Target Framework to .NET Framework 3.5 wasn’t enough. I had to edit the .csproj file by hand:

<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="3.5" DefaultTargets="Build" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <!— ... -->
  <ItemGroup>
    <!— ... -->
    <Reference Include="System.Core">
      <RequiredTargetFramework>3.5</RequiredTargetFramework>
    </Reference>
    <Reference Include="System.Xml.Linq">
      <RequiredTargetFramework>3.5</RequiredTargetFramework>
    </Reference>
  </ItemGroup>
  <!— ... -->
</Project>

Playing With SQL Server CLR Integration – Part II

Paulo Morgado — Thu, 11 Jun 2009 23:52:37 GMT

On my last post, I showed how to convert a property bag stored in text to a CLR Table-Valued Function.

I started thinking that I could retrieve the property values, but I couldn’t change them or add new properties.

Passing a table as a parameter is still not possible in SQL Server 2005. And that would force me to load the table into a variable, change it and load it back into the property bag.

Or I could create functions to Create, Update and Delete properties from the property bag.

Or I could just use XML. All it takes is creating a CLR Scalar-Valued Function to convert the property bag to an XML representation and another to convert the XML back to the property bag format.

It still makes me load the property bag into a variable if I want to change it, but it’s usable in SQL Server 2005, which was the targeted platform.

Converting the property bag to an XML document is easy using the previously created enumerator:

[Microsoft.SqlServer.Server.SqlFunction(
    Name = "ShortPropsToXml",
    IsDeterministic = true,
    IsPrecise = false,
    DataAccess = DataAccessKind.None,
    SystemDataAccess = SystemDataAccessKind.None)]
public static SqlXml ShortPropsToXml(string shortPropsText)
{
    var xml = new XElement("ShortProps",
        from shortProp in ShortPropsEnumerable(shortPropsText)
        select new XElement("p",
            new XAttribute("n", shortProp.Key),
            new XCData(shortProp.Value)));

    using (var buffer = new MemoryStream())
    {
        using (var xmlWriter = XmlWriter.Create(buffer, new XmlWriterSettings { CheckCharacters = false }))
        {
            xml.WriteTo(xmlWriter);
        }

        buffer.Position = 0;

        using (XmlReader xmlReader = XmlReader.Create(buffer, new XmlReaderSettings { CheckCharacters = false }))
        {
            return new SqlXml(xmlReader);
        }
    }
}

Converting the an XML document back into the property bag format is also easy:

[Microsoft.SqlServer.Server.SqlFunction(
    Name = "XmlToShortProps",
    IsDeterministic = true,
    IsPrecise = false,
    DataAccess = DataAccessKind.None,
    SystemDataAccess = SystemDataAccessKind.None)]
public static SqlChars XmlToShortProps(SqlXml shortPropsXml)
{
    var xml = XDocument.Parse(shortPropsXml.Value);

    var textBuilder = new StringBuilder();
    foreach (var item in xml.Document.Element("ShortProps").Elements("p"))
    {
        textBuilder.AppendFormat("[[[{1}]]]{0}{2}{0}", Environment.NewLine, item.Attribute("n").Value, item.Value);
    }

    return new SqlChars(textBuilder.ToString().ToCharArray());
}

Now it’s just uploading the new version of the assembly to the database:

ALTER ASSEMBLY [MyAssembly]
FROM '...\MyAssembly.dll'
GO

And defining the Scalar-Valued Functions in Transact-SQL:

CREATE FUNCTION [dbo].[ShortPropsToXml](@shortPropsText [nvarchar](4000))
RETURNS [xml] WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [Esi.SA.Encyclopedia].[ShortProps].[ShortPropsToXml]
GO

CREATE FUNCTION [dbo].[XmlToShortProps](@shortPropsXml [xml])
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [Esi.SA.Encyclopedia].[ShortProps].[XmlToShortProps]
GO

And it’s all set to go.

Now, given this property bag definition:

declare @text nvarchar(max)='[[[name1]]]
value1
[[[name2]]]
value2
[[[name3]]]
value3
'

I can change it into XML:

DECLARE @xml [xml] = dbo.ShortPropsToXml(@text)

Update:

set @xml.modify('replace value of (/ShortProps/p[@n="name2"]/text())[1] with "new value2"')

Insert:

set @xml.modify('insert <p n="name4">Value4.1
Value4.2</p> after (/ShortProps/p[@n="name2"])[1]')

Delete

set @xml.modify('delete (/ShortProps/p[@n="name3"])[1]')

Convert back to the property bag format:

print dbo.XmlToShortProps(@xml)

[[[name1]]]
value1
[[[name2]]]
new value2
[[[name4]]]
Value4.1
Value4.2

Query as a table:

select T.C.value('./@n', 'nvarchar(max)') as Name, T.C.value('.', 'nvarchar(max)') as Value from @xml.nodes('/ShortProps/p') T(C)

Name	Value
name1	value1
name2	new value2
name4	Value4.1 Value4.2

Unfortunately, the application uses characters that are invalid to SQL Server as XML characters and I can’t use it on that application. I’ll have to understand those values better and add other fields to the table and XML.

Playing With SQL Server CLR Integration – Part I

Paulo Morgado — Thu, 11 Jun 2009 23:41:14 GMT

I’m currently working with an application that stores a property bag in a SQL Server column like this:

[[[name1]]]
value1
[[[name2]]]
value2.1
value2.2
[[[name3]]]
value3

Don’t ask me why it’s done like this. It just is.

The application decodes this property bag into its inner structures and all works fine.

Sometimes I would like to query the database directly or do some reporting on those properties and just can’t.

So, I thought this was a good use case for SQL Server CLR Integration. I decided to build a CLR Table-Valued Function that would return the property bag as a two column table.

Parsing the property bag text can easily be achieved with a simple regular expression:

new Regex(
        string.Format(@"(?<Name>(?<=\[\[\[).*(?=\]\]\]{0}))\]\]\]{0}(?<Value>(([\s\S]*?(?={0}\[\[\[))|([\s\S]*?(?={0}$))))", Environment.NewLine),
        RegexOptions.Multiline | RegexOptions.ExplicitCapture | RegexOptions.CultureInvariant | RegexOptions.Compiled);

Ultrapico’s Expresso was a big help when developing this regular expression.

In case you don’t know, the way CLR Table-Valued Function are built is using an initial method to take the input and return an IEnumerable and row filler method the receives the enumerator item and outputs the column values.

Since these are really key-value pairs of strings, I decided to use )>) Structure" href="http://msdn.microsoft.com/library/5tbh8a42.aspx" target=_blank>KeyValuePair<string, string> instances to store each item and the enumerator became simply this:

private static IEnumerable<KeyValuePair<string, string>> ShortPropsEnumerable(string shortPropsText)
{
    return from Match m in shortPropsRegex.Matches(shortPropsText)
           select new KeyValuePair<string, string>(m.Groups["Name"].Value, m.Groups["Value"].Value);
}

And the implementation of the CLR Table-Valued Function is as simple as this:

[Microsoft.SqlServer.Server.SqlFunction(
    Name = "ShortPropsToTable",
    FillRowMethodName = "ShortPropsToTableFillRow",
    TableDefinition = "Name NVARCHAR(4000), Value NVARCHAR(4000)",
    IsDeterministic = true,
    IsPrecise = false,
    DataAccess = DataAccessKind.None,
    SystemDataAccess = SystemDataAccessKind.None)]
public static IEnumerable ShortPropsToTable(string shortPropsText)
{
    return ShortPropsEnumerable(shortPropsText);
}

public static void ShortPropsToTableFillRow(object item, out SqlChars name, out SqlChars value)
{
    KeyValuePair<string, string> shortProp = (KeyValuePair<string, string>)item;

    name = new SqlChars(shortProp.Key);
    value = new SqlChars(shortProp.Value);
}

To use this in SQL Server a few simple steps are need:

Load the assembly into the database:

CREATE ASSEMBLY [MyAssembly]
AUTHORIZATION [dbo]
FROM '...\MyAssembly.dll'
WITH PERMISSION_SET = SAFE
GO

Defining the Table-Valued User-Defined Function in Transact-SQL:

CREATE FUNCTION [dbo].[ShortPropsToTable](@shortPropsText [nvarchar](4000))
RETURNS  TABLE (
    [Name] [nvarchar](4000) NULL,
    [Value] [nvarchar](4000) NULL
) WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [MyAssembly].[ShortProps].[ShortPropsToTable]
GO

Enable CLR Integration:

EXEC sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

And all is set to go.

Now I can just query the property bag as a table:

SELECT
    e.[ID],
    e.[Name],
    e.[Class],
    e.[Type],
    p.[Name],
    p.[Value]
FROM
    dbo.Entity as e
    CROSS APPLY dbo.ShortPropsToTable(e.[ShortProps]) as p

Just for curiosity, for a little over 50000 entities (that can be retrieved on about 1 second on my laptop), I got a little under 630000 properties in les then 40 seconds.

40 seconds might seem a lot compared to the 1 second, but I would like to see better times using T-SQL. And develop and test the TVF in just a couple of hours.

Now, if only he DBAs would allow me to use it. It doesn’t fit into the company’s “security policy”, you know.

Microsoft URL Rewrite Module 1.1 For IIS 7 To The Rescue

Paulo Morgado — Mon, 01 Jun 2009 00:10:00 GMT

We are migrating the PontoNetPT community from an old .TEXT version to the latest Community Server (CS).

Because PontoNetPT has nearly 200 blogs with I don’t know how any posts, commentaries, trackbacks, etc., we are using the Community Server REST API.

The problem with using this API is that it doesn’t create the folder that CS can be configured to create with a default.aspx file for each blog created using it’s web site administration.

The importance of this folder and file is for IIS to be able to handle the requests http://<your community>/blogs/<your blog> or http://<your community>/blogs/<your blog>/ as if was a request to http://<your community>/blogs/<your blog>/default.aspx.

Fortunately, we are running on Windows 2008 and IIS 7 and all it took was to install the Microsoft URL Rewrite Module 1.1 For IIS 7 and configure it:

<configuration>
  <!--...-->
  <system.webServer>
    <!--...-->
    <rewrite>
      <rules>
        <rule name="Add Default.aspx to blog root URLs" stopProcessing="false">
          <match url="^blogs/([^/]*)(/?)$" />
          <action type="Rewrite" url="blogs/{R:1}/Default.aspx" />
        </rule>
      </rules>
    </rewrite>
    <!--...-->
  </system.webServer>
  <!--...-->
</configuration>

Typemock Is Launching The ASP.NET Bundle – Get Free Licenses

Paulo Morgado — Mon, 18 May 2009 22:57:00 GMT

Typemock is launching a new product for ASP.NET developers – the ASP.NET Bundle - and for the launch they are giving out FREE licenses to bloggers and their readers.

The ASP.NET Bundle is the ultimate ASP.NET unit testing solution, and offers both Typemock Isolator and Ivonna, the ASP.NET Isolator add-on, for a discounted price.

ASP.NET Bundle launch giveaway:
For the ASP.NET Bundle launch Typemock are giving away free licenses to bloggers and their readers.

How do I get the free license?

Are you a blogger, webmaster, or internet columnist? Get your free license by helping Typemock launch their new ASP.NET Bundle, the ultimate ASP.NET unit testing solution.

Post the text below on your blog (as is, with links) and tell them about it . If you are in the first 60 to mail us after posting, your license will be on its way!

If you have an ASP.NET dedicated blog, you'll get a license automatically (even if more than 60 submit) during the first week of this announcement.

8 bloggers will also get an additional 2 licenses (each) to give away / raffle to their readers or friends.

A few simple requirements:

You must own a website, blog space or internet column, older than 2 months, or get permission from the owner of such a site.
Your post must include the text below (including the links) between *Begin* and *End*.

*Begin*

Unit Testing ASP.NET? ASP.NET unit testing has never been this easy.

Typemock is launching a new product for ASP.NET developers – the ASP.NET Bundle - and for the launch will be giving out FREE licenses to bloggers and their readers.

The ASP.NET Bundle is the ultimate ASP.NET unit testing solution, and offers both Typemock Isolator, a unit test tool and Ivonna, the Isolator add-on for ASP.NET unit testing, for a bargain price.

Typemock Isolator is a leading .NET unit testing tool (C# and VB.NET) for many ‘hard to test’ technologies such as SharePoint, ASP.NET, MVC, WCF, WPF, Silverlight and more. Note that for unit testing Silverlight there is an open source Isolator add-on called SilverUnit.

The first 60 bloggers who will blog this text in their blog and tell us about it, will get a Free Isolator ASP.NET Bundle license (Typemock Isolator + Ivonna). If you post this in an ASP.NET dedicated blog, you'll get a license automatically (even if more than 60 submit) during the first week of this announcement.

Also 8 bloggers will get an additional 2 licenses (each) to give away to their readers / friends.

Go ahead, click the following link for more information on how to get your free license.

*End*

Once you post this in your site, just drop them a line at asp@typemock.com with your name and the post URL to get your free license.

Hurry up – this offer is limited to the first 60 bloggers, or to ASP.NET dedicated bloggers who will post this in the first week.

Make sure to follow their Blog, Site or Twitter for updates on this event.

LINQ With C# Book Is Finally Out

Paulo Morgado — Sun, 17 May 2009 23:39:51 GMT

It’s finally out!

The LINQ Com C# (LINQ With C#) book that Luís and I wrote is out. Well, mostly Luís than I.

This book, published by FCA, is targeted at anyone that already knows C# 2.0 and wants to know learn the new features introduced with C# 3.0 that made possible LINQ (Language INtegrated Query). The examples in the book are written in C#, but Visual Basic get be get from the book’s site.

	Title:	LINQ Com C#
	Authors:	Luís Abreu / Paulo Morgado
	ISBN:	978-972-722-547-7
	Number of Pages:	216
	Format:	17,0 x 24,0 x 1,3 cm (6.7 x 9.4 x 0.5 in)

The book can be found here:

Getting The Folder Path Into The Windows Search Results Windows

Paulo Morgado — Sun, 17 May 2009 19:45:07 GMT

Unlike it happened on Windows XP, on Windows Vista and Windows 7, the search results window doesn’t have (by default) a column with the folder path of the found items. Instead, it has a column named Folder in the form of Folder name (folder’s containing folder path).

I don’t know the reason behind this decision, I just know that it makes hard to sort the found files by its path.

Fortunately, the folder path of the found items can be added to the list view by right clicking on any list column and selecting the folder path to be shown.

Unfortunately, I couldn’t find a way to make it permanent. I need to do that for every search.

System Backup vs. Business Backup

Paulo Morgado — Sun, 10 May 2009 22:38:29 GMT

Most enterprises backup their data on a regular basis and stored those backups for some amount of time.

But, beyond restoring last day’s backup or part of a previous recent backup, are these backups real useful? Will they be useful 3, 4, 5 or 10 years from now?

Nowadays, the tendency is to virtualize almost every machine which increases the probability of a successful restore in the future. But some software packages come with timed and online checking licenses and one might end up with a successful restore of an unusable system.

And what if the hardware to run the system (virtualized or not) no longer exists?

The solution is to backup the business data (instead of the system data) in a interoperable format like XML (that can contain both data and metadata).

The added benefit beyond disaster recovery is system or application migrations.

That doesn’t mean that systems don’t need to be backed up. They do, but only for operational purposes.

Tip: Connecting To A Database Using Windows Authentication With Different Credentials Using SQL Server Management Studio

Paulo Morgado — Sun, 03 May 2009 23:34:19 GMT

It is a good security practice to use Windows Authentication to connect to SQL Server because you don’t need to write the password on some configuration file or registry entry.

This practice also brings governance benefits. Managing users becomes part of domain administration and not part of each SQL Server instance’s administration.

But this becomes an hassle to users (in this context, the user is someone that needs to perform administration task of some kind – a SQL Server Management Studio user) who need to connect to databases using different credentials.

One workaround is using the runas command:

runas /user:DOMAIN\USERNAME "C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"

But if you are working on an environment were there are several domains and your machine does not belong to the domain of the ser account you want to use, you’ll get the folloing error:

RUNAS ERROR: Unable to run - C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe
1787: The security database on the server does not have a computer account for this workstation trust relationship.

But not everything is lost. Because you want to make a remote connection, you can use the /netonly switch, and it works just fine:

runas /netonly /user:DOMAIN\USERNAME "C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"

SQL Server Management Studio still shows all the databases in the server (unlike what happens if you connect from a machine logged in as the user you want to access to the SQL Server instance), but ApexSQL Edit will only show the databases that account has access to.

Talking About Windows

Paulo Morgado — Mon, 27 Apr 2009 23:25:35 GMT

The Next Generation of Windows® is almost here!

Microsoft is setting up a video blog that will officially debut in early May called Talking About Windows.

This video blog provides insight on Windows 7 from the Microsoft engineers who helped build the product.

Additionally, it provides real-world commentary from IT professionals who have chosen to shift to a modern OS.

You can watch the videos and participate by commenting in the spaces below each video and help spread the word about this exciting endeavor to build community.

Too bad the site doesn’t provide a feed to subscribe to the videos on my Zune.

Watch. Connect.
www.talkingaboutwindows.com

MSDN Code Search Preview

Paulo Morgado — Mon, 30 Mar 2009 23:38:21 GMT

(Just read about this on CoDe Magazine)

Microsoft launched MSDN Code Search Preview.

The MSDN Code Search Preview lets you search for code in the MSDN Library, MSDN Code Gallery, and CodePlex.

You can read all about it on the about page or this post from the MSDN & TechNet Search Blog.

redgate .NET Reflector 5.1.5.0 Released

Paulo Morgado — Mon, 30 Mar 2009 22:47:35 GMT

Today redgate released version 5.1.5.0 of .NET Reflector.

It was a bumpy release. Early today, at work, as soon as @antsprofiler was announced the deployment of the new version I tried to upgrade and was offered an upgrade to the version 5.1.4.0 over and over again.

Early tonight, at home, when I tried to upgrade I was offered to upgrade to 5.1.5.0.

The funny thing is that, if I try to run it from C:\Program Files I get a UAC dialog the mentions Reflector 5.1.3.

Just moved to my C:\Program Files (Utils) and it all works fine.

Adobe Reader Fails To Upgrade/Install

Paulo Morgado — Mon, 30 Mar 2009 22:41:21 GMT

Yesterday I was offered an update to Adobe Reader (the latest version for Windows Vista is version 9.1) on my company’s laptop.

I accepted the update but it failed. It was late and a failed update to Adobe Reader was the least of my worries at the time.

Today, when I tried to use Adobe Reader, I noticed it had been uninstalled.

It’s not nice having to download and install something like Adobe Reader when I need to open a document, but the worst came when I tried to install it.

Adobe Reader requires elevated privileges to install but, due to the way the installer works, being a local administrator (I running Vista x64 Enterprise joined to a corporate domain) is not enough to install Adobe Reader on C:\Program Files (x86), because it was still failing.

I had already created C:\Program Files (Utils), to install the Windows Sysinternals tools (in particular, Process Explorer) and did the same for Adobe Reader.

Do you know what was the first name I thought of for this folder? C:\Program Files (Bad)

Just blame it on Vista.

MIX09 Session Presentation Slides

Paulo Morgado — Thu, 26 Mar 2009 01:51:20 GMT

On a previous post I introduced the feeds I created to subscribe to Mix09 session videos.

I’ve decided to also create a feed for the presentation slides:

PPTX: http://feeds.paulomorgado.net/PauloMorgado/Events/Mix09/SessionSlides

MIX09 Session Videos – How I Did It

Paulo Morgado — Mon, 23 Mar 2009 01:04:04 GMT

On my last post I introduced the feeds I created to subscribe to Mix09 session videos.

In case someone is interested on how I did it, here it is:

<%@ WebHandler Language="C#" Class="mix09" %>

using System;
using System.IO;
using System.Web;
using System.Linq;
using System.Xml.Linq;
using System.Net;
using System.Xml;

public class mix09 : IHttpHandler, IHttpAsyncHandler
{
    class WebClientOpenReadAsyncResult : IAsyncResult
    {
        private AsyncCallback callback;

        public WebClientOpenReadAsyncResult()
        {
            this.IsCompleted = true;
            this.CompletedSynchronously = true;
        }

        public WebClientOpenReadAsyncResult(AsyncCallback callback)
        {
            this.callback = callback;
            this.IsCompleted = false;
            this.CompletedSynchronously = false;
        }

        public object AsyncState
        {
            get { return null; }
        }

        public bool CompletedSynchronously { get; private set; }

        public System.Threading.WaitHandle AsyncWaitHandle
        {
            get { throw new InvalidOperationException("ASP.NET should not use this property ."); }
        }

        public bool IsCompleted { get; private set; }

        public Stream Stream { get; private set; }

        public void Completed(object sender, OpenReadCompletedEventArgs e)
        {
            this.IsCompleted = true;
            this.Stream = e.Result;
            if (this.callback != null)
            {
                this.callback(this);
            }
        }

    }

    private static Uri mixSessionsUri = new Uri("http://sessions.visitmix.com/RSS");

    private HttpContext context;

    private string type;

    #region IHttpHandler Members

    public bool IsReusable
    {
        get { return true; }
    }

    public void ProcessRequest(HttpContext context)
    {
        WebClient wc = InitializeRequest(context);

        if (wc == null)
        {
            return;
        }

        OutputFeed(wc.OpenRead(mixSessionsUri));
    }

    #endregion

    #region IHttpAsyncHandler Members

    public IAsyncResult BeginProcessRequest(HttpContext context, AsyncCallback cb, object extraData)
    {
        WebClient wc = InitializeRequest(context);

        if (wc == null)
        {
            return new WebClientOpenReadAsyncResult();
        }

        WebClientOpenReadAsyncResult ar = new WebClientOpenReadAsyncResult(cb);

        wc.OpenReadCompleted += ar.Completed;

        wc.OpenReadAsync(mixSessionsUri, extraData);

        return ar;
    }

    public void EndProcessRequest(IAsyncResult result)
    {
        Stream stream = (result as WebClientOpenReadAsyncResult).Stream;

        if (stream != null)
        {
            OutputFeed(stream);
        }
    }

    #endregion

    private WebClient InitializeRequest(HttpContext context)
    {
        this.context = context;
        this.type = context.Request.QueryString["type"];

        if (string.IsNullOrEmpty(this.type))
        {
            return null;
        }

        WebClient wc = new WebClient();
        wc.Headers[HttpRequestHeader.UserAgent] = "Required User Agent";
        return wc;
    }

    private void OutputFeed(Stream source)
    {
        XmlReader feedReader = XmlReader.Create(source);

        XDocument feed = XDocument.Load(feedReader);

        var rss = feed.Element("rss");
        var channel = rss.Element("channel");
        var title = channel.Element("title");

        title.Value = string.Format("{0} ({1})", title.Value, this.type.ToUpper());
        channel.Element("link").Value = "http://cli.gs/Mix09Sessions";

        foreach (var item in channel.Elements("item"))
        {
            string link = item.Element("link").Value;
            string session = link.Substring(link.LastIndexOf('/') + 1).ToLower();
            string enclosureUrl = string.Format("http://mschannel9.vo.msecnd.net/o9/mix/09/{0}/{1}.wmv", this.type.ToLower(), session);

            item.Add(
                new XElement("enclosure",
                    new XAttribute("url", enclosureUrl)));
        }

        this.context.Response.Write(feed.ToString());

        this.context.Response.ContentType = "application/rss+xml";

        HttpCachePolicy cache = this.context.Response.Cache;
        cache.SetCacheability(HttpCacheability.ServerAndPrivate);
        cache.SetExpires(DateTime.Now.AddHours(1));
        cache.VaryByParams["type"] = true;
        cache.SetValidUntilExpires(true);
    }
}

MIX09 Session Videos

Paulo Morgado — Mon, 23 Mar 2009 00:06:00 GMT

Updated (2009Mar25): Added MP4 feed.

Mix09 is over and I would like to watch some sessions.

I like to watch these kind of videos by subscribing them using the Zune Software as a podcast. I like the Zune Software because it downloads the “episodes” and I can watch them in any order and it never forgets where I was – even if I was watching it in the Zune.

Unfortunately, the only feed available (http://sessions.visitmix.com/RSS) does not include the videos.

Fortunately, the address of the videos is very predictable and I was able to build my own feeds:

The session videos are not available in all formats for every session (at least, not now) but, if you subscribe to all, you’ll get videos for all the sessions.

Subscribe and enjoy.

ASP.NET Futures: Control ClientID Generation

Paulo Morgado — Wed, 18 Mar 2009 01:44:50 GMT

ASP.NET is expected to have some improvements on the generation of client IDs.

Although this is a major improvement, it comes short by not allowing the generation of shorter client IDs for server controls.

My good friend Nuno Gomes has done some work on generating shorter client IDs for controls (*).

Jeff has taken it one step further with his How to shorten ASP.NET automatically generated control IDs article on CodeProject.

If you want to see a running example, check out http://www.biocompare.com/.

(*)