Michael Entin's notebook

Trust Services Lab updated

2012-07-10T01:56:00Z

Trust Services Lab has been updated today:

http://www.microsoft.com/en-us/sqlazurelabs/labs/trust-services.aspx

The update does not need registration code anymore (ignore "Get Invited" link - it is leftover from previous release and will be removed). Just download SDK, visit the portal to create server, and check the documentation and samples.

Note that if you are using previous release - the current and previous services are now both alive, as completely independent services. Each is only compatible with appropriate SDK. You cannot get to the previous service using new SDK or vise versa. Please update to the new SDK/service - it offers many new features (and the old service will be disabled some time in future).

Microsoft Codename "Trust Services" Lab at RSA

2012-02-28T02:33:15Z

We have a Trust Services booth at the RSA conference in San Fancisco - good time to come, learn more and see several cool demos.

Microsoft Codename "Trust Services" Lab

2012-02-03T20:07:00Z

I did not post for a long time, was mostly working on internal and not-yet-released projects.

But today the project I've been working recently has shipped our first Lab:

Microsoft Codename "Trust Services"

Trust and Security have been hot topics for the public cloud since its inception. Corporate IT departments and CIOs have repeatedly expressed concerns over the loss of control associated with moving various levels of sensitive data to a public cloud. At the same time, the overall benefits of a public cloud are tremendous and continue to gain momentum. This means that many organizations have a pressing need to migrate to public cloud infrastructure in spite of ongoing concerns about security.

Encryption is one of the fundamental required tools for protecting data in the cloud. However, when you start encrypting data, you are facing with question how do you manage encryption keys and encryption policies? There are several options (you will probably recognize the products using them):

Have keys owned by the cloud as well, but stored separately from data (Server Side Encryption) - this provides a bit of improvement, but not much of a real security
Use a single key that unlocks the data (Simple Encrypting Client) - secure, but not manageable, you have to give the same key to all the parties, have no way to revoke keys when one of the parties leaves, etc
Have an on-premise security middleware - secure, but not very useful, since now all the users (even mobile users) have to go through this middleware - you lose most of the benefits of the cloud, plus have to purchase and maintain on-premises hardware and software
Store keys, authorizations and encryption policies in the cloud, but signed and encrypted with individual keys given to each party - so that each party can locally verify authenticity of the policies and only authorized parties have access to and are able to decrypt the sensitive data

You can guess the Trust Services follows the latest approach, and provides a unique combination of end-to-end application level encryption and power of the cloud to roam encryption keys, while leaving the owner in full control of his data. It enables data driven applications to work with sensitive data, securely stored in different cloud-based storages while continuing to maintain control over access to this data.

Today we shipped the service (sign up link), the client SDK and four samples of client applications utilizing the service+SDK.

Check out the video at the Trust Services Lab Link, then read more at the Trust Services TechNet article.

Computer display size calculator

2011-07-07T07:31:00Z

Surprisingly, could not find quick tool to compare various screen sizes and resolutions.

It is useful to understand why a modern 14 inch display (with 16:9 ratio) is almost a centimeter shorter vertically than older 12 inch (with 4:3 ratio).

So here it is:

Diagonal	inches	or select
Resolution	x	or select

Results:

Screen area: square inches.

Width: , height: inches.

Pixel density: pixels per inch.

PowerPivot beginner journey 3

2011-02-15T23:38:00Z

PowerPivot lesson #3 - if the data can't be refreshed, it should be in Excel, or copied to PowerPivot.

I added zip code data from CSV file by loading to PowerPivot, and discarded the CSV since I did not plan to update it. Bad idea. PowerPivot wants to update all data when you do Refresh All, and missing CSV file prevents it from refreshing all other tables too.

What I should have done: load data to my Excel file, then import it to PowerPivot from Excel. Or load data to temporary Excel and copy/paste it to PowerPivot. Later solution is better, since it avoids data duplication.

PowerPivot beginner journey 2

2011-01-29T06:00:00Z

I was exploring the data, and found lots of interesting info from my data set. But I could not find how to join some tables in PowerPivot, and after looking at the source data - it turned out my source data used GUID columns, stored as binary, for row IDs - and PowerPivot silently hid it. I would expect it at least show the column and tell me it can't use it. Even better - offer convertion options. But PowerPivot does not even divulge column existense.

The lesson - learn your data before importing it to PowerPivot, afterward it might be too late.

The workaround is of course easy - use SQL query rather than table import, but tedious since PowerPivot makes you re-run the wizard for every single table. Here is the query just in case:

select *, convert(VARCHAR(40), guid, 1) AS GuidStr from MyTable

Then I've imported data, added relationships, and started to create calculated columns. One of the DAX expressions got wrong results, and I realized I missed one relationship which would make the expression return correct result. I've tried to add relationship, but PowerPivot did not let me do it, failing with some very obscure error message (An unexpected error occurred, file 'xmvsquery.cpp', line 1739, ...). Only once I've deleted that calculated column, I could create relationship, then add the column back, and finally get the correct results.

Second lesson for me - tell PowerPivot about all the relationships before doing anything with the data.

Overall, I find PowerPivot useful, but such problems make it way less useable as exploratory tool - you've to know all your columns and all your relationships in advance, and tell PowerPivot about everything. If you miss something you might need to go back and start from scratch, e.g. delete a table with all the calculated columns, and re-import it again to get binary column, or delete the calculated columns to be able to define relationships.

Undocumented PowerPivot installation requirement

2011-01-28T10:13:00Z

Trying to use PowerPivot, just found undocumented PowerPivot installation requirement: Excel .NET Programmability Support.

If PowerPivot silently fails to load, and in Event Viewer you've got an event like below, run Office Setup / Add or Remove Features, then check .NET Programmability Support under Microsoft Excel.

Here is the event you might find in event viewer:

Customization URI: file:///C:/Program Files (x86)/Microsoft Analysis Services/AS Excel Client/10/Microsoft.AnalysisServices.Modeler.FieldList.vsto

Exception: Customization could not be loaded because the application domain could not be created.　

************** Exception Text **************

Microsoft.VisualStudio.Tools.Applications.Runtime.CannotCreateCustomizationDomainException: Customization could not be loaded because the application domain could not be created. ---> System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.IO.FileNotFoundException: Could not load file or assembly 'Microsoft.Office.Interop.Excel, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c' or one of its dependencies. The system cannot find the file specified.

File name: 'Microsoft.Office.Interop.Excel, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'

at Microsoft.AnalysisServices.Modeler.FieldList.ThisAddIn..ctor()

Making Asynchronous Programming Easy

2010-10-30T07:30:00Z

Great post by Somasegar about upcoming language support for async programming:

http://blogs.msdn.com/b/somasegar/archive/2010/10/28/making-asynchronous-programming-easy.aspx

I guess soon the idea hack of using C# iterators as poor-man's language support (that caused me to start this blog :)) will finally be deprecated.

SSIS custom component performance tidbit

2010-05-25T05:27:00Z

I was helping a friend of mine to debug performance issue with his SSIS package. The package was pretty simple – he needed to read data, shard it using some partitioning scheme, then insert the results into 8 SQL destinations. The hashing algorithm used for partitioning was rather complex for conditional split, so he created a simple custom transform component that calculates the partition ID and adds it as a new column; the package then used this column in conditional split and finally there were OLEDB destinations for each shard.

But performance was not good – it took about 10 minutes to push ~32mln rows.

I started with simplest debugging technique – remove destination to check if it is the cause, then remove conditional split, etc. We quickly found the problem is caused by the custom component. But it is very simple:

public override void ProcessInput(int inputID, PipelineBuffer buffer)
{
    while( buffer.NextRow() )
    {
        byte[] guid = buffer.GetBytes(m_inputColumn);
        int partition = Partition(guid);
        buffer.SetInt32(m_outputColumn, partition);

        buffer.DirectRow(ComponentMetaData.OutputCollection[0].ID);
    }
}

Can you guess why is it so slow?

There are many potential inefficiencies here, e.g. due to the buffer API we’ve to allocate and copy byte array for every row. But the code that took most of the overall execution time was

    buffer.DirectRow(ComponentMetaData.OutputCollection[0].ID);

Why so slow? You see, the PipelineBuffer object is optimized for performance, but ComponentMetaData object and various collections of inputs/outputs/columns/etc are mostly for design time and were not optimized for performance at all. And there is a good reason for this: these collections do not change during runtime, so one should not call them at runtime. So we moved their usage to PreExecute and then used the cached output ID:

public override void PreExecute ()
{
    m_outputID = ComponentMetaData.OutputCollection[0].ID;
}

and somewhere in ProcessInput(..) -

    buffer.DirectRow(m_outputID);

Actually, this particular component did not need to call DirectRow at all, and using default exclusion group was enough, so we finally removed it. But if you do need to call it – cache the output ID in advance. Our performance has improved about 20 times by this simple change.

When April Fools jokes become reality

2008-12-11T20:02:00Z

Couple years ago Charles Petzold wrote about C# Application Markup Language - XML syntax for C#. Go read it if you've not seen it yet.

Well, it was a fool day's joke of course (the publishing date at the bottom hints about it).

But yesterday, when playing with Windows Workflow Foundation rules, I've looked at the ruleset file, and discovered exactly that - huge XML file that looked familiar :), very close to Charles joke. This is how WWF internally stores the simple one-line rules that one types in the editor. Luckily for us, we don't have to type this XML, and WWF convert the human-readable C# into this monstrosity itself, so it is more an internal details than a language humans would ever use.

The way NOT to write HTTPS server

2008-12-05T10:28:00Z

Note: posted under rant tag, so you are warned ;)

I've just got a new wi-fi router, DIR-655 from DLink. Seems like a nice router, but at least one feature is just plain horrible broken. And it is security feature, which makes me wonder how secure the rest of the code is.

The router can be accessed and managed using HTTP server, like most any other one. It also has an option to enable HTTPS server, which was probably supposed to make management more secure. This is not very important if you connect from local network, but very important if you do remote management from the internet.

So I decided to enable it and connect using HTTPS. Internet explorer immediately complained that there are problems with certificate and advised me against proceeding :). IE warned me that (1) the certificate cannot be validated, (2) the certificate has expired, and (3) the certificate was issued to incorrect site. I proceeded nevertheless, checked the certificate, and indeed - it is self-signed, expired in September 2008, and issued to site www.dlink.com, which is obviously different from 192.168.0.1 :). Worse, I asked other guys with same router to compare certificate hash - it turned out all the routers are shipped with the same certificate!

I can understand the self-signing nature of the certificate - obviously DLink cannot put real certificate to the router. But why has it expired, shared by all routers, and most bizzar - why it indicates www.dlink.com as site name?

They could have easily generated an individual certificate for each router, issued to correct internal IP address for internal-facing server and to DynDNS name for internet-facing server. It does not add any hardware cost; the software could just generate a random self-signed certificate the very first time the router boots with a new configuration. User could then configure his browser to trust this particular certificate, and know he connects to his own router, not any of the thousands other routers with same certificate.

The way they did this feature, it is totally broken and makes no sense at all.

Does buffer.NextRow() skips the first row in a buffer?

2008-10-19T10:33:40Z

I got a follow up question to my old post regarding enumerating rows in SSIS buffer, that suggested using following code to process rows in custom SSIS transform:

while (buffer.NextRow())
{
// do something with the row
}

Here is the question:

buffer.NextRow() moves the pointer forward, so following your code to the letter will make you skip the first row.

Does it? Actually, no. It behaves like many other enumeration interfaces, e.g. COM's IEnumVariant and .NET's IEnumerator, so I'll quote documentation from IEnumerator::MoveNext:

After an enumerator is created or after the Reset method is called, an enumerator is positioned before the first element of the collection, and the first call to the MoveNext method moves the enumerator over the first element of the collection.

Can you guess why it was designed this way? Let's think what would happen, if the enumerator was positioned on the first element (row in SSIS case) initially. How would we know if the first element exists at all? The enumerator would have to provide another property, like EndOfCollection - making everything more complicated. Note that as explained in my linked post, buffer.EndOfRowset() is not such an indicator. It does not tell you that enumerator finished enumerating rows in current buffer, it tells you that current buffer is the very last buffer you will receive.

With enumerator initially positioned before first row, you call NextRow() which will immediately return false if the collection is empty, and position iterator to first row if it exists. So the code is correct.

A note regarding my original post: the SSIS team found that this change caused too many problems for the users, so the final release of SSIS 2008 reverted back to the SSIS 2005 behavior. Interestingly, it was not a simple undo of code change, as the data flow engine has been substantially rewritten, but a new code to simulate the old behavior. Thus the "wrong" code will keep working in 2008. I still recommend changing it according to my previous blog - I think my loop just looks cleaner :).

It's Not Easy Being BI

2008-06-18T22:45:00Z

A very funny post by Matthew Roche, for everybody working in Business Intelligence:

http://bi-polar23.blogspot.com/2008/06/it-not-easy-being-bi.html

Lookup multiple rows?

2008-06-07T11:37:00Z

Can SSIS Lookup do what this user wants it to do?

I have a problem with a lookup output, I get this warning: The Lookup transformation encountered duplicate reference key values when caching reference data. I know what it is, but I don't like to avoid this warning, I'd like to get all the rows (two in this case) that the lookup output provides me.

Unfortunately, no - the reason is that Lookup transform is synchronous, i.e. it does not add new rows or remove rows*, it just modifies the values - i.e. it can't produce two output rows for one input row.

It would of course be possible to make an asynchronous Lookup, or provide an option, but the current Lookup is complex enough, that I think more options would kill it :)

If you need this functionality, you can use Merge Join transform.

Notes
*What happens with the rows that are redirected to "not found" output in SSIS 2008? They are not deleted from the buffer (synchronous transform can't do it), they are just marked as belonging to the other path, and the components on the main path do not see them.

Application termination when user logs off

2008-04-05T03:02:00Z

Do you know how windows terminates all the applications when user logs off? I did not think too much about this, and assumed that it is a normal process - after all the WM_QUERYENDSESSION and WM_ENDSESSION processing, the application main window closes, posts WM_QUIT and the application quits in a regular way.

But a recent bug reported for one of my GUI applications caused me to look deeper. The application settings, normally saved at application's exit, were not saves if user logged off, and this made me look closer at what happens at logoff.

This was a managed application with related code in Main() function that looked like this:

static void Main()
{
    LoadPreferences();
    Application.Run(new MainForm());
    SavePreferences();
}

This works great when the application is closed normally. But if user logs off, the application closes, but the preferences are not saved - the code following Application.Run is never called at all.

What happens? Does Application.Run throw any exception that causes the following code to be bypassed? This was ruled out rather quickly by debugger. I then assumed something in Windows Forms calls ExitProcess in response to WM_ENDSESSION, or maybe default message handler does this - but that was proved wrong too. A repro with unmanaged ATL code showed it is not related to managed libraries at all.

Finally my colleguae debugged this issue a little deeper, and found that CSRSS is blatantly terminating the process after it processed the WM_ENDSESSION event. Here are the outlines of the whole sequence (for non-console applications).

The user selects Start/Log off and then selects “OK” in the confirmation dialog. This calls ExitWindowsEx(EWX_LOGOFF); if during the OK button click, the CTRL key was down, it also adds the EWX_FORCE flag which makes it ignore the result of the WM_QUERYENDSESSION message (as if the applications always returned TRUE). ExitWindowsEx(EWX_LOGOFF) causes roughly the following activity to occur in the logon session’s instance of CSRSS:

For each process in the session
{
    For each UI thread in the process
    {
        For each top-level window in the thread
        {
            Send WM_QUERYENDSESSION and get back the result
            Wait with a short timeout and show an “End program” dialog if it takes too long; if the user says “kill”, call TerminateProcess on the process and continue the “for process” loop
            If EWX_FORCE was not specified and WM_QUERYENDSESSION returned FALSE, break (and continue "UI thread loop");
        }
        // Note that even if one window in one thread returned FALSE to WM_QUERYENDSESSION, windows in the other threads in the same process are still sent WM_QUERYENDSESSION
        bool bDoShutdown = (all threads agreed (i.e., all windows returned TRUE to WM_QUERYENDSESSION)) or EWX_FORCE
        For each UI thread in the process
        {
            For each top-level window in the thread
            {
                Send WM_ENDSESSION(bDoShutdown);
                If (bDoShutdown) wait for return (wait with a short timeout and show an “End program” dialog if it takes too long; whatever the user says, consider that the return from WM_QUERYENDSESSION)
            }
        }
        If (bDoShutdown) TerminateProcess on the process
}
I have omitted some details, in particular related to no-UI and console processes, but the general picture should be clear. Probably the Windows guys decided that graceful cleanup is not needed when user logs off, and all the application are closed.

Since the application is forecefully terminated, the application should not rely on being able to execute any code after the message loop. Even classes like SafeHandle are not finalized. All really important cleanup and termination code should be executed when the main form closes, or by providing a handler for the WM_ENDSESSION message . Another option (although I did not try it) is to catch WM_ENDSESSION message and terminate the message loop, then exit the application gracefully - although this goes contrary to Windows design of killing applications fast to ensure quick log off.

Update: Raymond Chen described the reasons for this behavior:
http://blogs.msdn.com/oldnewthing/archive/2008/04/21/8413175.aspx

Configuring .NET for running SSIS packages from custom applications

2008-03-15T01:45:00Z

If you execute SSIS packages from custom applications, you own the application and thus you are responsible for configuring .NET runtime properly to get the maximum performance.

.NET configuration is usually performed using .exe.config files, so it is a just matter of providing good config file. How do you know what is good? The simplest way is to look at the config file that SSIS provides, and copy the appropriate settings. Now let's take a look at DTExec.exe.config provided with SQL Server 2008 and discuss the choices made by SSIS team.

requiredRuntime - this line simply tells which version of .NET you want to use. 2.0 or above should be good.

gcServer - DTEXEC uses the "server" version of garbage collector. Server GC performs better for typical SSIS load, especially on multiprocess machines. This is important if your SSIS package uses managed transforms (e.g. ADO.NET source, script transform, or custom transform written in .NET).

disableCommitThreadStack - by default .NET commits the thread stacks (i.e. reserves memory for stacks - usually from page file). But SSIS creates a lot of threads, while typically uses little stack space, so it performs better if stack memory is not immediately committed when the thread is created. With this option your application might perform a bit better and require smaller page file (note that anyway, the page file is not really used until needed). The drawback of this choice is that application might fail if Windows is totally out of memory, and application can't extend its stack. But in this situation something has already gone bad, and it is probably better to fail fast in this case anyway.

generatePublisherEvidence - this tells .NET runtime the DTEXEC does not use Publisher evidence, and thus .NET does not have to verify authenticode signatures. This increases the startup performance a little bit, but mainly prevents problems that may occur when authenticode checks certificate revocation list.

Now that you know these options made by SSIS team, you may test and decide whether they are appropriate for your application as well, and copy them to your application's config file if needed.

P.S. Also make sure you create SSIS package using MTA thread, see Matt's blog for details:
http://blogs.msdn.com/mattm/archive/2007/09/14/running-packages-from-custom-applications.aspx

Random() is only random if you are using it right

2008-02-05T11:53:00Z

I like the quote "With great power comes great responsibility" when used in regards to .NET - .NET gives one great powers, but use it wisely and know how this stuff works.

Recently I saw code (it was written by a guy interviewing to our team) that demonstrated interesting problem with incorrect usage of Random class. The code generated a random position for an object, tested if it satisfied some condition; if it did not work - generated another random position, tested the new position and so on until it generated something that did work. The code to generate each position was pretty simple: create new instance of Random class, use it to generate 4 random integers. The percentage of failed position was not very big (but noticeable), the code to test the position was rather fast, and we needed just 10 good positions to create complete configuration (all this was about a variant of Battleship game).

But it took very long to generate the whole configuration - about a second on average.

Playing with this code, I suddenly found that moving Random object to a member variable, so it is re-used, rather than creating new Random object for each position, made the code 10000 times faster! Well, obviously I expected some savings since it creates less objects, but ten thousand times? My first reaction - is the Random really such a heavy object?

It turned out the problem was more interesting, and caused by the usage of default constructor. As MSDN puts it, if you use default constructor the "seed is initialized to a value based on the current time." It actually uses GetTickCount() as the seed. Can you now spot the problem? The Random() constructor was very quick, but during the period of time when GetTickCount() returns the same value (which is about 20ms for most chipsets), all the Random objects created this way will have the same seed and generate the same positions! Instead of trying new positions, the code tried the same position again and again until the GetTickCount() returned a new value. So don't generate too many Random instances - just one will behave much better in most cases.

SSIS event handler threading

2008-02-01T23:25:00Z

I've got an interesting question/statement about event handlers:

Tasks fire the same EH at the same time. My understanding is all EHs fire at the same time (Parallel).

If I understand the question correctly, the package has an event handler that can handle multiple events, and these events fire at about the same time. What happens?

Well, I did not work much with event handlers, so let's experiment and test how it works. This is really simple. I've create a package with two dummy script tasks A and B (not connected with any precedence constraints) that fail (by returning failure) and OnError event handler on the Package. The event handler contains a simple script task that prints the name of the event source:

MsgBox(Dts.Variables("SourceName").Value, MsgBoxStyle.Information, "Event Handler")

What will be the result of executing this? Will we get two message boxes at the same time, or sequentially two message boxes?

The answer is - two message boxes popup sequentially one after another, but never at the same time. Ssometimes A is the first, sometimes B. The event handler (like any other task) is not reentrable and only a single instance of an event handler can be running at a time, thus the second event has to wait for the event handler to finish processing the first event before processing the next one.

Functional sort in C#

2007-12-11T22:46:00Z

On an internal mailing list, we were discussing functional languages, and this Haskell sort code:

qsort [] = []
qsort (x:xs) = qsort (filter (< x) xs) ++ [x] ++ qsort (filter (>= x) xs)

While trying to explain how this code works (which is very different from what it looks like to C++/C# programmers due to lazy evaluation) I've come up with following C# code (with Linq) that is logically similar to Haskell version. Obviously, Haskell code is much nicer though.

static IEnumerable<T> Qsort<T>(IEnumerable<T> s) where T : IComparable<T>

{

IEnumerator<T> e = s.GetEnumerator();

if (e.MoveNext())

{

T x = e.Current;

foreach (T t in Qsort(s.Skip(1).Where(y => y.CompareTo(x) < 0)))

yield return t;

yield return x;

foreach (T t in Qsort(s.Skip(1).Where(y => y.CompareTo(x) >= 0)))

yield return t;

}

SQL 2008 & VS 2008

2007-12-05T09:42:00Z

Currently SQL Business Intelligence Development Studio (BIDS) and all the project types (AS, IS and RS) live in Visual Studio 2005. So don't try to open a solution that contains IS project in VS 2008 yet. What about final SQL 2008 - now that Visual Studio 2008 is released - what are the plans for BIDS and support of BI projects VS 2008?...(read more)

VALVe/Steam horrors

2007-11-24T02:20:00Z

(Update: VALVe has removed the worst recommendations that I described below. I don't know if my article had any influence on this. They still want to run the Steam as a service though).

I originally planned to use this blog for work-related stuff only, but the VALVe drove me mad, so I decided to write this.

I run Vista on my home PC (and at work too, actually), and all of us run as non-admins (not Vista's protected admin, but real non-admin). My son plays Counter Strike, and thus has to run Steam. It worked mostly fine, until recently it started to ask to install Steam as a service. Well, a service for internet-facing software seems not good. Before installing, I decided to find out more about it, and the finding are much worse than I've ever expected. Here is an official FAQ from VALVe that I've found:

http://support.steampowered.com/cgi-bin/steampowered.cfg/php/enduser/std_adp.php?p_faqid=460

Here are some of the recommendations from this FAQ:

Go to: Start > Run and type in: cmd

Type in the following:
net localgroup Administrators /add Local service

Restart your computer.

To put it simply, they ask to add Local service account to Administrators group! Apparently, they did not bother to find out the list of specific permissions they need, but simply figured out that administrator permissions "fix" the problem. That alone would be pretty bad, but they did even worse. Instead of creating special account for Steam or using some account that already has administrative permissions, they want to add account shared by multiple services to Administrators group, thus making not only their service, but all the services running on the machine much more dangerous and less secure.

Then VALVe gives even stranger recommendation:

If during this process you receive the error: System error 5 has occurred. Access is denied Please follow these instructions and then try the above steps again:

Go to Start > Control Panel > System & Maintenance > Administrative Tools > Local Security Policy.

In the left pane, expand Local Policies > Security Options.

Double-click Network Security LAN Manager Authentication Level.

In the drop down list, change the default setting (NTLMv2 only) to Send LM & NTLM - use NTLMv2 session if negotiated.

Changing LanMan level for Vista Home users to completely unsecure level? Maybe I'm missing something, but I can't see any reason to do this. This setting affects authentication to remove computers (mostly affects NT domain, should be of no concern to internet game), it would not help one to avoid local Access Denied while performing the steps above. It is not only completely unsecure, but IMO it also does not make any sense at all.

So these guys
1) don't understand anything about security,
2) can't write software that does not require administrative privileges,
3) don't even try to use the specific minimum list of permissions,
4) ask user to make completely unreasonable and unsecure configuration changes to OS configuration, that affect not just Steam service, but the whole machine,
5) want to run network-facing service with admin rights.

Now would you trust the guys who are clueless about security, obviously can't write secure code, and apparently don't care about YOUR machine security at all, to run network-facing service with admin rights on your machine? I don't. I think I'll try stay away from them from now on.

P.S. As with everything else in this blog, this post represents my personal opinion, and does not represent the view of my employer.

SQL Server ETL Survey: Win a Zune!

2007-11-21T02:11:00Z

Improve SSIS and win a Zune:

https://mscuillume.smdisp.net/Collector/Survey.ashx?Name=SqlETLSurvey2

SQL 2008 November CTP is available

2007-11-19T20:56:00Z

Get it from https://connect.microsoft.com/SQLServer

One of the improvements in this build is the ability to persist Lookup reference data and use non-OLEDB sources for Lookup.

Deploying packages

2007-11-07T12:48:00Z

How can one deploy packages programmatically? Here is the original question -

Is it possible to deploy a package programmatically? We have an application which has a work flow for approval of object. If the object (ssis package) is approved by the concerned authority it has to be deployed to sql server. How can this code be integrated into the application?

First, what does it mean to deploy a package? The package is simply a document (DTSX file). SSIS utilities (DTEXEC, DTUTIL and other) natively support three locations for such files: the actual file in the OS file system, a row in SQL table, or virtualized file in SSIS storage (which wraps the previous two). If you use custom application to run your packages, you can store them anywhere you want (e.g. application resources) - and use LoadFromXML to load it.

Since package is just a file, in most cases simply copying a file to destination directory location, or to SQL table is all it takes to deploy a package. One can use SSIS API (like SaveToSqlServer) to do this, run DTUTIL, or copy file using Win32 or .NET APIs.

In some cases it is necessary to make some adjustments to the package, e.g. if you have multiple packages calling each other - modify the parent package to correctly reference new location of child package(s). This can be done using SSIS API as well, but it is easier and usually better for maintainability to do this using SSIS configurations - edit the configuration values instead of the package itself.

SSIS Lookups modes or leaky abstractions

2007-10-03T22:31:00Z

I've got a question about SSIS Lookup,

how-to change Partial or No_Cache mode in Lookup task ?

I could give a quick answer, but wanted to explain what I think we did wrong in SQL 2005, and how are we planning to fix this. Before continuing, I recommend everyone to read this article:

Joel Spolsky, The Law of Leaky Abstractions
http://www.joelonsoftware.com/articles/LeakyAbstractions.html

What that article has to do with SSIS Lookup and the question in particular?

If you look at the Lookup UI, you wound not find any reference to Partial or NoCache mode (it does show up in Properties panel, but most users miss it). The idea was that you don't have to know about all these details. The UI abstracts away these modes by providing advanced options, where you can enable Memory Restrictions. If have enough memory, you would keep defaults. But if you have less memory, you would enable Memory Restrictions, and specify how much memory Lookup should use. It is all clean and simple, and ideally you don't need to know about all these internal modes and how Lookup operates. I wish :).

And of course, like most abstractions, this one is very leaky. It is even mentioned in some Books Online articles, even though BOL does not mention the Lookup modes when describing Lookup Editor.

Internally, SSIS Lookup has three caching modes: Full, Partial and None. They dramatically change how the Lookup operates. Full cache is used if you don't check Enable memory restrictions, None if you check Enable memory restrictions but don't select Enable caching, and Partial cache is used when you check both Enable memory restrictions and Enable caching.

In Fully Cached mode, Lookup fetches all the data into memory first, then processes its input, matching (using internal binary comparison) input data with reference data. Obviously, SQL is not used anymore to process all this data. So you don't need SQL indexes, but you better have enough RAM to hold all the reference data.

In Partial or No Cache mode, Lookup does not fetch any data in advance. It starts with empty internal cache. For each input row, it looks into the cache, and then if it does not find data, it executes SQL SELECT statement and saves the result in the cache. You set the size of the cache for Partial cache mode in the Lookup properties. For No Cache mode, only the very last row is cached (which makes No Cache a bit of confusing name, but it's been decided it does not make much sense to throw away this last row, the memory for it has already been allocated anyway). Obviously, you need an index on the reference table for this to perform well. Also, SQL uses its own collation rules that are usually different from internal binary comparison.

Now you probably see why this abstraction was very leaky. The results could be different (SQL collation vs. binary collation), you need to create indexes for partial/no-cache mode, etc. And hopefully, now you know enough about what is hidden by this abstraction to use Lookup efficiently and correctly.

P.S. In SQL 2008, we decided to stop hiding this abstraction, and provide direct access to the Lookup cache modes. Here is the first page of new Lookup editor:
http://blogs.msdn.com/photos/michen/images/5267231/original.aspx
I'll probably blog about other new items you see here - Cache connection manager and handling of no-match entries later.