CODEWITHSUNDEEP
sql-server-2008sql-server-express
Tomas Aschan
Tomas Aschan

Reputation: 60644

Unexpected error message from aspnet_regsql

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)

Upvotes: 5

Views: 4954

Answers (5)

Vladimirs
Vladimirs

Reputation: 8609

Ensure that in SQL Server Configuration Manager -> SQL Server Network Configuration -> Protocols for YOUR_SQL_SERVER_INSTANCE_NAME is enabled TCP/IP. NB. for applying this change need to restart YOUR_SQL_SERVER_INSTANCE_NAME

Upvotes: 1

JRC
JRC

Reputation: 21

If you want to use the graphical interface, you need to make sure the user you are logged in as has the default db in SQL set to 'master' so it can connect directly to the sysdatabases table.

Upvotes: 2

hqrsie
hqrsie

Reputation: 413

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

Upvotes: 0

Talley
Talley

Reputation:

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]

Upvotes: 0

Tomas Aschan
Tomas Aschan

Reputation: 60644

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).

Upvotes: 10

Related Questions