If you have played with SQL Server on recent builds (Beta2 and beyond) of Windows Vista, you might have noticed that database connections using Windows authentication usually fail because of UAC (User Account Control) – even if the account you are using is in the Administrators group, and regardless of what account your SQL Server service runs under. You can see this in Visual Studio, e.g., when you try to create a new DB connection.
This behavior (which will be fixed by the SQL team somewhat later towards the release of Windows Vista) can also be observed when you try and work with Windows Embedded Studio (the XP Embedded toolset) on a Windows Vista machine, as I do currently. When I installed Embedded Studio on my fresh build 5472, putting the component dependency database in my SQL Server 2005 installation instead of the default MSDE that comes with the XPe tools (but the DB engine does not matter for observing the effects described here), everything ran through successfully since I had run setup.exe as admin explicitly (“Run as administrator” from the context menu) – always a good idea for setups under UAC. When I later launched the component database manager, I got this:
It took me quite a while to figure out what was going on. When I saw the same behavior in VS2005, I found out that I could connect using SQL Server authentication, so it must have to do with Windows auth and the account that is used. I finally found the cause by also running the component database manager “as administrator”, and viola – no problems.
So, what can you do about this? One solution, as described here, is to completely turn off UAC, which is not what you should want, because UAC gives you more security in environments where you are connected to the outside world (web browser, e.g.). Another one is to right-click on the program icons and chose “Run as administrator”. If you want to make this permanent, go find the app’s executable in Windows Explorer, select “Properties” and check “Run this program as an administrator” on the “Compatibility” tab:
(This also applies to Target Designer, VS2005, and similar apps that make connections to a database through Windows auth.) Of course from now on, every time you launch the app, you are asked for your permission to execute this action by Windows since you are performing an administrative task. Bit of a nuisance, but you can get used to it.
Hope this helps to bridge the time until Windows Vista has RTMed, and a fix for SQL Server is made available.