sql server 2005

One Year Left for SQL Server 2005 Support

Richard Green on

Many enterprises are still dealing with the challenges of completing Windows XP and Windows Server 2003 migrations. Whether you are moving to Windows 7 or Windows 8.1, perhaps even running the gauntlet on Windows XP and hedging your bets for Windows 10 later this year on your clients all the while, evaluating and testing your line of business applications and servers on Windows Server 2012 R2, there is a lot to deal with.

There’s nothing like a little added pressure to throw into the mix and that is why as of 12th April 2015, there is one year left on the extended support status of SQL Server 2005. This notice effects all editions of SQL Server 2005 including 32-bit and 64-bit versions, remembering of course that later versions of the Microsoft database engine are 64-bit only.

With databases and their associated servers being critical to the underpinning of your applications, making the right choices to move these databases is a big decision. If for example, your current database server is a 32-bit server then not only will you have to move that to a 64-bit version of SQL Server but also a 64-bit operating system and that may mean new hardware required if the server only has a 32-bit processor to work with. There is also the question of virtualization as back in 2005, many people wouldn’t have dreamed of virtualizing a database server but today, it’s a commonly done thing . We even have Database as a Service solutions available in the public and private cloud such as SQL Database in Microsoft Azure.

Once you’ve decided on a target architecture platfom, the talk may move on to questions such as storage types, SSDs and flash cache devices such as the Fusion-io ioDrive as things have certainly moved on in storage since your SQL Server 2005 system was first deployed and once you’ve had those conversations, you can think about high availability options such as failover clustering, mirroring or AlwaysOn High Availability, the latter being new to exiting SQL Server 2005 users and offering a fantastic high availability solution.

I think the SQL Server 2005 issue is going to be quite a wide-spread one as in my travels to customer sites and on projects, I see a lot of SQL Server 2005 in the field still, running production systems and some of these systems may themselves no longer be within support so contacting the vendors for information about support for later versions of SQL may make for interesting work. If the vendor themselves has ceased trading then finding out whether that application will support SQL Server 2012 or SQL Server 2014 will be down to you and a test environment.

The Case of the Failed SQL Login

Richard Green on

I was asked to investigate a problem with SQL Server 2005, whereby Administrators logging on to the SQL Server couldn’t login to the SQL Server Management Studio. An attempt to login to the Management Studio (or connect by SQLCMD for that matter) would get an Error 4064: Cannot Open Default User Database.

I Bing Searched the Error 4064 and found lots of information on it, but nothing really pointed to a cause or resolution to the problem, however I knew that the problem was related to the master system database.

The vast majority of sites suggested things but none of these worked for me. Being that my skill with SQLCMD is somewhat slim, I decided to aim for the Management Studio.

I knew that there was a problem with the master system database, and being that the master database is the database used to generate user connections, I thought I would try to login with a different database.

SQLLogin1

As per the image above, expanding the available options in SQL Server 2005 gives you the Connect to Database option. I set this to model being that I knew the system database model would exist and I didn’t know the names of any other databases on the server.

Excellent, it got me in. So the first place I looked was to make sure that the the master database was present. I figured it would be pretty difficult for SQL to start without a master database attached but anything is possible I guess.

Sure enough the database was there, but upon trying to view it’s properties, I got the same Error 4064.

I then tried to take a look at the server properties and I got the same Error 4064 again.

So I was poking around MSDN when I found the sp_defaultdb command, and I ran the following:

use master
go
sp_defaultdb [DOMAINUSER], master
go

 

I logged out of the Management Studio and back in, but this time without the Connect to Database statement selected. I was able to login correctly to he normal master database because I had set my own.

This works for one user, but you don’t want to be going around setting the master as the default database for every user in the company otherwise your going to end up with a very long and complicated SQL configuration on your hands.

With the master database now correctly loaded for my user, I was able to view the server and database properties. First up was the master database and everything here looked good, so I moved on to the server. Whilst browsing through the server permissions, I saw this:

SQLLogin2

Notice how the default database location fields are blank? This is all under the Configured Values mode, so I switched to Running Values and they where blank too.

I entered the correct values into these paths (the SQL Server defaults in this case) and it’s working perfectly – Users are able to login (permission granted of course) without needing to specify a default database.

And that closes the case of the failed SQL Server login.