ServerConnection and Login failed for user. Reason: Attempting to use an NT account name with SQL Server Authentication

Today I had to work with the ServerConnection class.

This class provides a method to specify the connection to the Server class.

So usage would be like this:

# http://sqlblog.com/blogs/allen_white/archive/2008/04/28/create-database-from-powershell.aspx
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')  
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.ConnectionInfo')  

$sqlServer = "server"
$username = "username"
$password= "password"

$serverConnection = new-object Microsoft.SqlServer.Management.Common.ServerConnection($sqlServer, $username, $password)

$server = new-object Microsoft.SqlServer.Management.Smo.Server($serverConnection)

Write-Host ("SQL Version: {0}" -f $server.Information.Version)

$server.databases | % { Write-Host $_.Name }

Now this works for SQL accounts, but not for domain accounts.

My username was in the form of DOMAIN\username, but that failed.

Checking the SQL Server log, it yielded this:

Login failed for user. Reason: Attempting to use an NT account name with SQL Server Authentication
Login failed for user. Reason: Attempting to use an NT account name with SQL Server Authentication

So to use the domain account with this object you need to create the $serverConnection like this AND you need to specify your username in the form of: [email protected] (FQN). Entering DOMAIN\username doesn’t seem to work.

$sqlServer = "server"
$username = "[email protected]"
$password= "password"

$serverConnection = new-object Microsoft.SqlServer.Management.Common.ServerConnection($sqlServer)

$serverConnection.ConnectAsUser = $true
$serverConnection.ConnectAsUsername = $username
$serverConnection.ConnectAsUserPassword = $password

You need to use the ConnectAsUsername and ConnectAsUserPassword to use domain accounts and set the ConnectAsUser property to true.

When I connect with those options I get the access I need.

You can verify it by executing the following query:

$conn.ExecuteScalar("SELECT SUSER_NAME() as [foo]")

Which nicely yields:

[email protected]

Have a good one,

-Kristof

TeamCity reporting that it’s out of diskspace

I installed TeamCity on a server (just the professional edition).

The server was installed under D:\TeamCity, with it’s (only) agent under D:\TeamCity\buildAgent.

I then started creating a project. At one point I modified the VSC Checkout rules, and then stuff went downhill.

First of all, a VSC root is not under some sort of revision control like build configurations, which is a pain in the *** to track your changes. Even the Checkout rules aren’t (which are build configuration specific).

Anyway, this was the error:

Free disk space requirement
[18:18:07][Free disk space requirement] Removing files to meet 3.0Gb of free disk space required for directory D:\TeamCity\buildAgent\work\cc94054af2903311 (only 0.0b is free now).
[18:18:07][Free disk space requirement] Removing files to meet 3.0Gb of free disk space required for directory D:\TeamCity\buildAgent\temp (only 12.7Gb is free now).
[18:18:07][Free disk space requirement] Free disk space requirement of 3.0Gb could not be met for directory D:\TeamCity\buildAgent\work\cc94054af2903311 (only 0.0b is free)
[18:18:07][Free disk space requirement] Free disk space requirement of 3.0Gb could not be met for directory D:\TeamCity\buildAgent\temp (only 12.7Gb is free)

Weird errors, because the disk actually had >3.0GB free (not 3.0Gb, that’s a typo on their side).

I checked all the disks, maybe it tried to write to the C: for temp files, but that one had enough space too.

Upon further investigation I saw I made a typo in the Checkout rules of a particular VCS root which caused the fail:

I set the rule like this:

+:Foo=Bar

As you can see, it misses the >

So the correct one should be

+:Foo=>Bar

It’s very annoying they don’t have error checking for that.

Have a good one,

-Kristof

ASP.NET MVC Mobile: where is my style?

I was playing with ASP.NET MVC Mobile to start something quickly for my phone.

I then updated the NuGet packages, and my style was GONE.

Seems that jQuery 1.9.* is incompatible with jQuery.Mobile 1.2.

According to the website 1.3 is out, so the package should soon be there, so until then you can revert from jQuery 1.9.* to 1.8.3 with the following code in your Package Manager Console:

uninstall-package jQuery -Force # ignore that some have dependencies on jQuery
install-package jQuery -Version 1.8.3

jQuery downgrade

Have a good one,

-Kristof

WebClient not sending credentials? Here’s why!

TL;DR version here.
This post applies to more than just GitHub, read the rest to see the behavior!

I was playing with the GitHub API (more specifically generating a new OAuth token).

So what you need to do, as per the documentation, is to post a certain JSON string to https://api.github.com/authorizations, with Basic Authentication. I’m going to use the WebClient class for this.

This is the JSON string that you need to post:

{
  "scopes": [
    "repo"
  ],
  "note": "API test"
}

Now this is the code I used:

var content = new 
			{
				scopes = new[] { "repo" },
				note = "API test",
			};

var webClient = new WebClient
	                {
				Credentials = new NetworkCredential("*****", "*****"),
	                };

// JsonConvert is from NewtonSoft.Json, very handy!
string serializedObject = JsonConvert.SerializeObject(content);

string reply = webClient.UploadString(new Uri("https://api.github.com/authorizations"), "POST", serializedObject);

dynamic deserializedReply = JsonConvert.DeserializeObject(reply);

Console.WriteLine(deserializedReply.token);
Console.ReadLine();

However, when using this piece of code I always get a 404 not found.

Reading through the Github API documentation yields the following:

There are three ways to authenticate through GitHub API v3. Requests that require authentication will return 404, instead of 403, in some places. This is to prevent the accidental leakage of private repositories to unauthorized users.

(emphasis mine)

So there’s a good change that we just hit Github’s security through obscurity, so we do normally get a 403.

In fact, I tested it with Github Enterprise, and that one just returns a 403, so that’s how I figured out that the URL I was calling is correct (Github Enterprise doesn’t hide information like the regular does):

403 forbidden from a Github Enterprise instance

Let’s try the same code on a Simple IIS website with basic authentication:

Screenshot (12)

I then simplified the code to just download the contents of the website, with a simple GET:

var webClient = new WebClient
	                {
		                Credentials = new NetworkCredential("*****", "*****"),
	                };

string reply = webClient.DownloadString(new Uri("http://localhost/CredentialTest"));

Console.WriteLine(reply);
Console.ReadLine();

So that gives me the response (Default.aspx contains ‘Hi, it works’).

Now what’s going on? Is it the POST that conflicts?

Using the same code, but instead of DownloadString, I upload some arbitrary piece of text with UploadString which by default uses POST.

var webClient = new WebClient
	                {
		                Credentials = new NetworkCredential("*****", "*****"),
	                };

string reply = webClient.UploadString(new Uri("http://localhost/CredentialTest/Default.aspx"), "somerandomstuff");

Console.WriteLine(reply);
Console.ReadLine();

Please note that I post directly to Default.aspx. IIS doesn’t allow postings to directories (I’m sure you can enable it).

Anyway, this also works.

Next step? I was thinking that WebClient maybe only sends the credentials over when it detects that the machines are in the same domain / workstation?

Let’s find out with Fiddler.

I first monitored the flow for the console app to IIS and I was surprised to see that there were actually two requests, and what’s even more weird is that the first request doesn’t send the credentials (notice I still use POST, to mimic our code to connect to GitHub):

First request to IIS

Instead of returning a 403 on the file, IIS nicely returns a 401 with the WWW-Authenticate header:

first response

The WebClient is then smart enough to resend the request WITH the credentials:

second request

And then the server nicely responds with a 200, and the contents are sent (notice the picture are JUST the headers).

second response

This flow is always the same, whether it is GET or POST (what’s weird is that when you want to post a 2GB file, you send it, server replies 401, and you need to send the 2GB file again…).

Now that we know that our WebClient is behaving correctly, I decided to go and look at the request and response from GitHub:

The first request (like with IIS), doesn’t contain the Authorization header:

First GitHub request

However, in contrast to IIS, GitHub doesn’t play nice. It doesn’t send a 401 with a WWW-Authenticate header, it just returns 404 (or 403 on Github Enterprise).

First and only response from GitHub

For GitHub it is perfectly valid to send a 404 if it doesn’t want to disclose information.

The only problem is just that the WebClient doesn’t know what to do and thus, we need to do stuff ourselves!

We need to manually inject the headers when calling Github (or any webserver) at the first request:

var content = new 
			{
				scopes = new[] { "repo" },
				note = "API test",
			};

var webClient = new WebClient();

// replace webClient.Credentials = new NetWorkCredentials("*****","******") by these 2 lines

// create credentials, base64 encode of username:password
string credentials = Convert.ToBase64String(Encoding.ASCII.GetBytes("*****" + ":" + "*****"));

// Inject this string as the Authorization header
webClient.Headers[HttpRequestHeader.Authorization] = string.Format("Basic {0}", credentials);

// Continue as you are used to!
string serializedObject = JsonConvert.SerializeObject(content);

string reply = webClient.UploadString(new Uri("https://api.github.com/authorizations"), "POST", serializedObject);

dynamic deserializedReply = JsonConvert.DeserializeObject(reply);

Console.WriteLine(deserializedReply.token);
Console.ReadLine();

And we have our token!

Borat Great SuccessHave a good one!

-Kristof

Powershell, Where-Object and capturing the output

Again, one that thing I couldn’t find because there is just no documentation on Powershell.

Try this:

$foo = SomethingThatResturnsAList

$foo = $foo | Where-Object { $_.Name -like "*something*" }

PassOnFooToSomethingElse -List $foo

Awesome, every sane developer would think: right, that would work. Except in Powershell.

It appears that $foo on line 5 is $null

WHAT?

Okay, let’s surround it with braces, maybe that’ll solve stuff:

$foo = ( $foo | Where-Object { $_.Name -like "*something*" } )

But that doesn’t work either.

Now for the solution, I’d love to point you out to a link on the world wide web where you can view the documentation, but honestly, I just can’t find it…

The solution is to replace the 3th line by this: $( … ), like this:

$foo = $( $foo | Where-Object { $_.Name -like "*something*" } )

And that works.

Happy coding & have a good one,

-Kristof

Powershell Remove-Item and symbolic links

Let’s say you’ve got a symbolic link which points to another folder and you want to delete the symbolic link through Powershell. Prepare for some weird stuff! Consider the following test script:

New-Item "SymbolicTest" -Type Directory
Set-Location "SymbolicTest"
New-Item "Source" -Type Directory
Set-Location "Source"
New-Item "Test1.txt"  -Type File
New-Item "Test2.txt"  -Type File
New-Item "Test3.txt"  -Type File
Set-Location "../"
# now some cmd since Powershell can't natively create symbolic links
cmd /c mklink /J `"Target`" `"Source`" 
ls

Let’s check the structure: dir in powershell Okay, so no difference, both are considered directories. Now let’s check with cmd: dir in cmd Okay, weird, cmd understands it. Now let’s try to remove the Target link. Remember, we only want to delete the link, not the contents of the Target folder (and thus implicitly not the contents of the Source folder). With Remove-Item you can delete items. Let’s try it out:

Remove-Item .\Target

This yields the following output:

deletion powershell

Wait what? Deletion of the children. Just for the sake of it I typed Y. This yielded another error: deletion powershell force prompt Okay, again, with -Force powershell content gone Target was gone, however also the contents of Source. Auch? Now what? Well, reading through the documentation of Remove-Item I found nothing about symbolic links.

So what now? Seems that Powershell doesn’t have knowledge about symbolic links. It wasn’t taken into account. He just follows the link like it’s a normal directory.

So what’s the solution? I decided to use the good old cmd (from Powershell!):

cmd /c rmdir .\Target

rmdir works!

Marvelous. Problem solved!

Actually, in retrospect, since I needed to use cmd to create the link, it would have made more sense to also just delete the link with cmd!

Have a good one,

-Kristof

Powershell: switch is not boolean

I spent a lot of time on this, mostly because of the lack of documentation on Powershell, I wonder why there isn’t one like for C#.

Anyway, back to the title, 2 different words, their behavior in a function is the same, but the invocation is not. Let me show you:

function MyAwesomeFunction
{
    param
    (
        [string] $foo,
        [string] $bar,
        [switch] $someVariable 
    )

    Write-Host "someVariable = $someVariable"

    if($someVariable)
    {
        Write-Host $foo
    }
    else
    {
        Write-Host $bar
    }
}

MyAwesomeFunction -foo "Foo" -Bar "Bar" 

Invocation of this script yields the following result:

PS D:\Personal\Desktop> D:\Personal\Desktop\test.ps1
someVariable = False
Bar

Now if we change the ‘switch’ on line 7 to ‘bool’:

function MyAwesomeFunction
{
    param
    (
        [string] $foo,
        [string] $bar,
        [bool] $someVariable #or boolean
    )

    Write-Host "someVariable = $someVariable"

    if($someVariable)
    {
        Write-Host $foo
    }
    else
    {
        Write-Host $bar
    }
}

MyAwesomeFunction -foo "Foo" -Bar "Bar" 

The result is the following

PS D:\Personal\Desktop> D:\Personal\Desktop\test.ps1
someVariable = False
Bar

Right, the same. Both times the variable ‘someVariable’ is False.

However, when we want to set the variable ‘someVariable’, then the invocation differs. If we invoke it like this on the bool version:

MyAwesomeFunction -foo "Foo" -Bar "Bar" -someVariable $true

That works fine.

If we invoke that one on the switch version, we are presented with the following error:

Positional argument error

So I would suggest that you use the bool version for internal functions in modules, ones that when you can explicitly set the someVariable to true or false, whereas the switch version is more useful of external use when you’re invoking a module and want to print more messages (like -Verbose), no need to print -Verbose $true.

Have a good one,

-Kristof

NuGet package restore with local feeds

At my work we have our own NuGet feeds. And we tell our developers to disable the official NuGet feed. Why you may ask?

Let me explain with an example:

A new project is started, developer X decides, let’s use Ninject. So he downloads Ninject from the official feed, which is now at 3.0.1.something. He uses it. After a while a request comes in, it’s discussed, and internally we already developed a package to aid said developer with those tasks. However that package has a specific dependency on Ninject 2.something.something.something. Now what? The developer will have the hassle to downgrade his Ninject package, and more importantly, he might need to rewrite code because the API of Ninject 2 and 3 differ.

Hence why we would like to constrain the packages used. We set up our own feed, and if needed we copy packages from the original feed to ours.

So this is the setup: 2 feeds in the package manager, one is the official one, that’s unchecked, and ours:

Next up: a developer starts a new Visual Studio solution. He doesn’t want to commit the packages he uses to source control, so he decides to go with Nuget Package restore.

So what’s going on? Let’s ask Fiddler:

Fiddler looks for the package in the feed.

It’s looking for the NuGet.Build package. Fine. I downloaded that one, again, we don’t to use the official feed at all.

So I put that package in our NuGet feed, and tried to enable package restore again:

An error occured while configuring the solution to restore NuGet packages on build. The remote server returned an error: (404) Not Found.

What? I make a copy of a package and now it gives me an error?

Let’s look again with Fiddler:

Fiddler cannot find the package on the server

Aha, it finds the package, but appearently it’s using a hard coded path to find the package on the server. Since we don’t have the same URL structure it fails.

So it seems that I have to stop my investigation here. Since we cannot (and do not want to) move our NuGet url we decided that we just pull NuGet.Build and NuGet.CommandLine from the official NuGet server.

We deleted it from our server.

Update: This is also happening because we use NuGet.Server. This one doesn’t host the stuff on the same location and the NuGet feed is hardcoded in the NuGet dll. But then again, NuGet makes an assumption. And that’s bad!

Null lists behavior in Powershell

Powershell is weird. We know that.

But this blows my mind.

Consider the following line of code:

$result = Get-ChildItem "foo*"

foreach($bar in $result)
{
	Write-Host ("Item name: {0}" -f $bar.FullName)
}

What will this print?

When a matching file is found it will print its full name.

What if it doesn’t print? Every sane person will say: nothing. Powershell is not sane. This is the result:

Property 'FullName' cannot be found on this object. Make sure that it exists.
At C:\Users\Kristof Mattei\Desktop\test.ps1:9 char:39
+     Write-Host ("Item name: {0}" -f $bar. <<<< FullName)
    + CategoryInfo          : InvalidOperation: (.:OperatorToken) [], RuntimeException
    + FullyQualifiedErrorId : PropertyNotFoundStrict

What?

Why on earth would the foreach start?

The list is $null, not even empty. Do I need to prefix every single foreach with a null check to make sure it doesn’t start?