Unexpected error message from aspnet_regsql

Question

I am trying to use the aspnet___regsql utility to install all the membership tables in a database on my local SQL Express 2008 instance. I can connect without problems in Management Studio, but when I try to list the databases in the aspnet_regsql interface i get an error message saying:

Failed to query a list of database names from the SQL Server.
Invalid object name 'sysdatabases'

What am I doing wrong? How do I fix this?

(Please tell me there's a smoother way than reinstalling the thing... :P)

Answer 1

that table is only available in the master database

Answer 2

I have solved the problem by bypassing the graphic interface and performing the same action directly in the command prompt, with flags specifying all options. They should look like this:

Connect using SQL Authentication

C:\>Windows\Microsoft.NET\Framework\v2.0.50727\aspnet_regsql.exe -S myHostingServer -A all -d myDatabase -U myUserID -P myPassword

Connect using Windows Authentication

C:\>Windows\Microsoft.NET\Framework\v2.0.50727\aspnet_regsql.exe -S myHostingServer -A all -d myDatabase -E

The difference is at the end of the lines, where SQL Auth. has -U myUsername -P myPassword while Windows Auth. has -E (which flags for Trusted Connection).

Answer 3

By default on Vista the current user on the machine is not a member of SysAdmin on SQL Server 2005/2008.

So here is the trick.

1-Open Sql Server Surface Area Configuration tool from C:\Program Files\Microsoft SQL Server 2005/2008.

2-When the screen pops up click on Add New Administrator.

    -on top right make sure that User to provision=Username of the pc
    -on your left (Available Privileges) select Member of SQL Server SysAdmin and click on the right arrow (>)
    -click ok and you should be ready to go.

My name is Talley Ouro,Developer in Raleigh,NC Blog: http://talleyblogs.blogspot.com/ Email:[email protected]

Answer 4

This issue is generally caused by leaving "Server" to be your machine name instead of the name of your SQL server instance. It's tricky to figure out because they autofill the text box to your machine name, which is needed but incomplete. This may lead you to think "Server" refers to the computer itself when they really mean SQL server instance. If you are connecting to your local machine you can generally append to their entry with the name of your SQL instance and everything will work as intended

IE LOCALHOST must become LOCALHOST\SQLEXPRESS or whatever you name your box\sqlserver