Archive

You are currently browsing the archives for the Administration category.

Apr

25

Unable to add certain Active Directory users as Windows Logons to SQL Server 2008 on Windows Server 2008 R2

By pcurd

Last week I faced an issue where I was unable to create Windows Logons for SQL Server 2008 from Active Directory users on a Windows Server 2008 R2 server when a Windows Server 2008 server was able to.

The error was a 15401 error which are quite common and usually mean your Server Principal names are wrong, however I had checked this and knew it was not an authentication issue. See my post on Server Principal Names for more details on this. Plus I was able to add other users. The error was “Error 15401 – Windows NT user or group ‘domain\username’ not found”.

There is a Knowledge Base article which describes the problem (with a hotfix) but the symptoms are not the same as the knowledge base so I hope this makes it easier to find. The knowledge base article is KB976494 (Error 1789 when you use the LookupAccountName function on a computer that is running Windows 7 or Windows Server 2008 R2) http://support.microsoft.com/kb/976494.

Reblog this post [with Zemanta]

Apr

22

Access 97: The database engine can’t find Error

By pcurd

When linking to a SQL Server table in Access 97 today I came across this weird error:
“The database engine can’t find TableName. Make sure it is a valid parameter or alias name, that it doesn’t include invalid characters or punctuation, and that the name isn’t too long.”

As the table name was reasonably small, and I had longer table names already linked, I became suspicious.

The problem was indexes, if the total length of the table name plus the length of the name of the longest index is longer than 64 characters, you get that error. Making the index name smaller solved the problem.

Apr

1

Find Query Plans on Microsoft SQL Server

By pcurd

Today I was asked by Luke Smith a question about Query Plans in Microsoft SQL Server and how to return the Cached Query Plans to check they are decent.

I began to think about the DMV (Dynamic Management View) sys.dm_exec_cached_plans and whilst that includes some important information, it doesn’t include the plan itself.  So then I turned to another DMV, sys.dm_exec_query_plan passing it the plan_handle from the first DMV.

Finally, to be useful as a diagnostic tool I wanted the query text so I added in a third DMV sys.dm_exec_sql_text again passing the plan_handle. I then sorted by usecounts which does what it says on the tin and limited to those that are not system procedures, are actually plans, as opposed to records from parse trees or extended stored procedures, and have been used more than 10 times.  This returned a lot of data for me so I limited to the top 100, but this could obviously be tuned to a different environment.

Running this in SQL Server Management Studio has the bonus of being able to click on the XML in the query_plan and view the plan graphically which is definitely better than the XML!
Read more »

Mar

4

Renaming Microsoft SQL Server servers and the effects on SPNs

By pcurd

Last week I saw a post on Simon Sabin’s blog about SQL Server service accounts and SPNs and made a comment about the importance of SPNs when renaming a SQL Server or migrating several servers into .. less than several. I felt this an area worth expanding a little more.

I’ll describe a hypothetical SQL Server infrastructure, changes to be made and how I would resolve the Kerberos issues that would result.

The base infrastructure:

A simple SQL Server environment with two Microsoft SQL Servers – SQLA and SQLB. The dataset on these two servers is different, no databases are shared. The domain for this company is “example” and the DC is “example.com” and so their usernames are formed “example/UserName”.

The new infrastructure:

A new SQL Server is purchased with power enough to run the entire dataset and it is to be called SQLA. The service account is to be a domain user called “sqlservice”.

However, there are a lot of applications that link to SQLB by name and recoding them all is considered too much work. (A classic example is Access which doesn’t make changing the source of tables easy without relinking)

A solution:

Migrate the total dataset to the new server, assign it a name of SQLA and take account of Simon’s SPN advice – i.e. use Network Service or a domain account to run the SQL Service. Use DNS to create a record for “SQLB” pointing to SQLA. If you want to be really fancy, assign the original IP address of SQLB as an additional IP on SQLA.

The result:

Connecting to “SQLA” via NTLM, SQL Database logins (assuming they were migrated too) and Kerberos works fine – as you’d expect. However, connecting to “SQLB” only works for NTLM and SQL Database logins – Kerberos fails along the lines of “Cannot generate SSPI Context”.

The solution:

As you may have guessed from the title of this article, the solution lies with SPNs. When you register for a Kerberos token you are doing so against a server name – and in this case, when talking to “SQLB”, that server name is wrong. So how does one send the correct name? Well in the example above, you can’t. You are stuck sending “SQLB”. The only solution is to make the name not wrong. To do this, we register another SPN against SQLA – effectively allowing it to understand and use the tokens made against “SQLB”.

The syntax is along these lines: (Please note, written from memory so some tweaking may be in order)

SetSPN -a mssqlsrv/SQLB.example.com:1433 example\sqlservice
SetSPN -a mssqlsrv/SQLB.example.com example\sqlservice
SetSPN -a host/SQLB.example.com
SetSPN -a host/SQLB

More details on SetSPN can be found on MSDN at http://msdn.microsoft.com/en-us/library/ms178119.aspx (Registering Kerberos Service Principle Names by Using Http.sys).

Nov

23

Powershell to run Get-MessageTrackingLog on multiple machines

By pcurd

I have an infrastructure with multiple Exchange 2007 servers and wanted to compile a list of all emails sent by two email addresses. Whilst Get-MessageTrackingLog did what I wanted, it only ran on one server at a time and would only search for one email address.

To get around this I wrote a PowerShell script to run Get-MessageTrackingLog for each sending email address and each computer. This is then combined into one CSV file for transfer to Excel.

$computers = "server1", "server2"
$senders = "Email1@domain.com", "Email2@domain.com"
$outputfile = "C:\output.csv"

$start = "DD/MM/YYYY HH:MM:SS"
$end = "DD/MM/YYYY HH:MM:SS"

$(foreach ($sender in $senders) {
$(foreach ($computer in $computers) {
get-messagetrackinglog -Sender $sender -Start $start -End $end -EventID "RECEIVE" -Server $computer | select Timestamp, @{Name="Recipients";expression={$_.Recipients}}, MessageSubject, Sender

})}) | sort-object -property timestamp | Export-CSV -path $outputfile -notype

Oct

31

Microsoft Office 97 on Windows 7

By pcurd

As per my last post we are testing some legacy systems on Windows 7.

One of my colleagues has begun the testing process with Office 97.

Beginning the process, it started off badly with a message ‘Are you sure you want to install this software from an unknown publisher’. Mm well, yes, I guess Microsoft, I do want to install Microsoft software.

During a regular install with binder, HTML editor and something else small I can’t remember removed there were 3 errors about there not being a default email client installed. As we plan to install Office 2003, including Outlook, over the top I wasn’t worried about these.

Access 97

The main important app for us is Access 97 so this was the first to be tested. It opened fine, even clippy seemed pleased with his new home. A few simple self contained databases with tables, queries and forms, with some vba module code, opened fine.

To be tested: We plan to test with non-form module code and reports next week. Then SQL links.

Result: So far all is working and response time is good.

(This post is still under construction)

Oct

31

Legacy software on Windows 7

By pcurd

One of the duties my department has is to evaluate new software and check for compatibility with existing systems and other software. With the release of Windows 7 in the last two weeks it therefore made sense to up the testing from a few packages on the RTM to our entire suite on the final code.

One of my colleages has begun this process this week and I will blog some of the findings as they happen. I will put up a post for each major package to aid the googlefu of the posts!

Please feel free to leave comments with any other experiences or findings.

Aug

4

Trigger Scheduled Tasks Remotely

By pcurd

Just had to trigger a windows scheduled task remotely and was struggling to find VBScript etc to do it when I found a rather natty tool – SCHTASKS.exe.

Very helpful tool that works locally or remotely and by file path or schedule name.

However, there is an odd bug listed here http://www.onnraves.com/2009/05/20/running-scheduled-tasks-from-the-command-line/ which stumbled me for a little while. If you get the error “The parameter is incorrect” this is definitely the place to go.

(To summarise in case of link rot, the “.” character must not be in your task name)