Alias created by cliconfg.exe (32-bit / 64-bit) don't work on 64-bit Windows 7

cliconfg.exe can be found in two locations at a 64-bit system when sql client tools are installed:
- [32-bit] C:\Windows\System32\cliconfg.exe
- [64-bit] C:\Windows\SysWOW64\cliconfg.exe

I wonder if anyone experience this.

Recently, I have moved my development machine to 64-bit windows 7 but I still have one project database running on 32-bit XP; the DB server is SQL 2005.

At my development box, I have SQL 2008 installed. I tried to use both 32-bit and 64-bit cliconfg.exe to create an alias but I cannot connect to that DB server on XP. In the SQL Server Configuration Manager, the alias created by both 32-bit and 64-bit cliconfig.exe are only shown up at "SQL Native Client 10.0 Configuration (32bit)" category.

[Updated on May 15, 2011] As a matter of fact, the entry is correct because the SQL2005 is running on 32-bit XP. Thus, the entry only shows up at 32-bit category. Indeed, this entry can only be used in .NET connection string for connection. Unfortunately, it cannot be used by SQLCMD (by default found at "C:\Program Files\Microsoft SQL Server\100\Tools\Binn") for remote connection. It was what I did last time. It failed.

If I use the non-32-bit client configuration in SQL Server Configuration Manager to create an alias, then the remote connection immediately works.

[Updated on May 15, 2011] Last time, I didn't check with .NET application. I only used SQLCMD -S <alias> for connection. Indeed, this entry only works for SQLCMD but fails for .NET remote connection.

In the registry, the one created by both cliconfg.exe will have an entry at

   HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\Client\ConnectTo

The one created by non-32-bit client configuration in SQL Server Configuration Manager will instead have an entry at the following registry:

   HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo

Currently I don't have time to investigate it. But I just wonder why the alias created by either 32-bit or 64-bit cliconfg.exe utility won't work. I thought each version of cliconfg.exe maintains a separate list of alias in the registry. Why are both currently pointing to the same registry?

[Updated: May 15, 2011] The best reason to explain why both two 32-bit and 64-bit cliconfg.exe utilities create the same alias & registry entries is because the SQL server running on XP is 32-bit. The alias created by the non-32-bit client configuration in SQL Server Configuration Manager won't work for .NET but SQLCMD. Only the entry shown up at "SQL Native Client 10.0 Configuration (32bit)" category works well in .NET ConnectionString. Again, the main reason of my case is that the SQL server is running on 32-bit.

dbreader and dbwriter

If you have execution permission error when you run your ASP.NET, assigning dbreader and dbwriter roles to the ASPNET user will not help, especially your application only uses stored procedures. dbreader and dbwriter serve no purpose for this. They are related to table access. In this case, you need the following statement (if you're using SQL 2005).

GRANT EXECUTE ON SCHEMEA::dbo TO ASPNET

Why GRANT EXECUTE ON SCHEMEA?

GRANT EXECUTE ON SCHEMEA::dbo TO ASPNET

By default schema is owned by dbo (any member of the sysadmin fixed sever role), which is different from db_owner (fixed database role).

If you don't specify schema on the table when you create it, the schema will be created under dbo privileges. The schema of the default LocalSqlServer database ASPNETDB is usually under dbo. Most of time, you need to run GRANT EXECUTE ON SCHEMEA statement.

What this statement does is to grant ASPNET

• to execute stored procedures in the dbo schema.

If you change your mind later after you have granted the EXECUTE permission, you can always use REVOKE to remove the previous granted (or denied) permission, e.g.,

REVOKE EXECUTE ON SCHEMEA::dbo TO ASPNET

You can also use DENY statement to prevent someone from having certain permissions.

Limitation highlight of SQL server editions

The following applies to SQL 2005 Editions.

Feature	Express	Developer or Enterprise	Workgroup or Standard
Is free?	Yes	No	No
Redistributable?	Yes	No	No
Work with Team Foundation Server?	No	No - Developer Yes - Enterprise	Yes
Concurrent Connection	5	No Limit - subject to @@MAX_Connections	No Limit - subject to @@MAX_Connections
Database size	4 GB	No Limit	No Limit
CPU supported	1	Maximum OS supported	2 - Workgroup 4 - Standard
RAM Supported	1 GB	Maximum OS supported	3GB - Workgroup Maximum OS supported for Standard

Others features comparison list can be found here.

How to attach MDF without LDF into SQL 2005 - Part 2

In my part 1, I mentioned to use VS.2008 to generate the data/schema script first and then import the data back to the database. Here is another way to attach MDF back to the SQL 2005 sever without LDF. In this approach, you don't need to install Visual Studio but you may need another tool for help depending on your result by running the following SQL statement.

In the following illustration, I will use AdventureWorks.mdf as an example.

Execute the following statement to attach your MDF on SQL Management Studio:

USE master
GO
CREATE DATABASE AdventureWorks
ON PRIMARY (FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks.mdf')
FOR ATTACH_REBUILD_LOG
GO

If everything works fine, you may get the similar message below and have your database attached after execution:

File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_log.ldf" may be incorrect.
New log file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_log.LDF' was created.

Otherwise, you may have the following similar error:

File activation failure. The physical file name "C:\Users\<SomeUserName>\Documents\MyCode\Test\App_Data\AdventureWorks_log.ldf" may be incorrect.
The log cannot be rebuilt when the primary file is read-only.
Msg 1813, Level 16, State 2, Line 3
Could not open new database 'AdventureWorks'. CREATE DATABASE is aborted.

Depending on how or where you get your MDF file (because your MDF may not be detached properly), your LDF rebuilt may fail and so may your database attach command. In this case, you need to download and use the free tool SQL Server Express Utility (SSEUtil) to interact with the server and create the LDF file for you. This tool also works well with the full version of SQL Server 2005. There are two (2) big steps you need to perform in order to make your MDF finally available on SQL Management Studio.

Step 1: Use SSEUtil to attach MDF into the database server

First, you can copy the MDF to the location where you usually store for your SQL server if you like, e.g., C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\, or keep wherever it is now.

Then at the command prompt, run sseutil's attach database command. Before doing it, please be sure to add your path to sseutil.exe. Otherwise, you need to use its absolute path name,., e.g., c:\util\sseutil.exe.

sseutil -s .\SQLExpress -a "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks.mdf" AdverureWorks

where

• -s to specify the instance of SQL server.
• -a to attach the database by specifying its MDF file path name.
• The last parameter is optional. It is the name of database you want to call (e.g., AdvantureWorks).

There are several options that you can use with sseutil, like -user and -pwd to specify the usr name and its password for connection. Please see the readme file of SSEUtil for details.

or

You can use sseutil console to interact with the server.

• To start a sseutil console, at the command prompt, type
  sseutil -c
  
• You will see
  Console mode. Type 'help' for more information.
  1>
• To attach a MDF, type
  !attach "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks.mdf" AdventureWorks
• Type quit when you're done. This step is necessary to enable you to add the database into the sysdabases in the next step by removing the lock on file.

Step 2: Add MDF into sysdatabases

Now you have MDF attached to the server and have LDF recreated. But the database will still remain hidden from SQL Server Management Studio. In order to make it visible in SQL Server Management Studio, you need to go through the database attach wizard to add the database back to sysdatabases; or in the management studio, run either one of the following SQL statements:

• USE master
  GO
  EXEC SP_ATTACH_DB
  @filename1=N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks.mdf',
  @dbname=N'AdventureWorks
  
  or
  
  
• USE master
  GO
  CREATE DATABASE AdventureWorks
  ON PRIMARY (FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks.mdf')
  FOR ATTACH
  GO
  

I hope that you find this guide useful.

How to attach MDF without LDF into SQL 2005

SQL 2005 does not support DBCC REBUILD_LOG. Thus, you cannot use it to rebuild the LDF. Here is how I do to attach a database only using MDF without LDF present.

If you are only interested in attaching MDF to the SQL 2005 server without data extraction and replication, please see my part 2, which works without Visual Studio installed.

I use VS.2008 to do the trick. VS.2005 doesn't have this ability. This method probably may work in Visual Web Developer 2008 Express Edition.

Open VS.2008 and do the followings:

1. New a Web site or use the existing one.
2. Add the file to the project's App_Data folder.
3. Right click this folder in the Solution Explorer and select Add Existing Item...
4. Locate the MDF file you want to use.
5. Then click Add.
6. Double-click on the MDF file that you just added; it will open Server Explorer for you.
7. Locate and right-click the MDF file data connection on Server Explorer.
8. Select Publish to provider...
9. Go through the wizard; when the wizard asks you where to publish, I select Script to file. It will generate a SQL file that contains all the database schema and its data.

On the SQL 2005 Server (mine is Express version),

1. Create a database with the same name as your MDF.
2. Run the SQL file you just created by Server Explorer in VS.2008.

Your database should be revived now!