Can't connect w/Visual Studio 2005
I'm right-clicking on Data Connections in Server Explorer, and selecting Create New SQL Server Database. I've turned on the SQL Browser, so I'm able to select the instance from the combo box. I select Use Windows Authentication, type the database name to create, and click OK.
I'm getting this error:
"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)"
I've followed the instructions here, but with no luck:
http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx
Is there something else I need to do?
Thanks,
Jeff
[1188 byte] By [
InteXX] at [2008-2-4]
I'm assuming three things here:
- The database is embedded in your VS project.
- Your project is stored under My Documents
- Your connection string does not include User Instance = True
You can check the connection string by right-clicking on the Data Connection and clicking Modify Connection. When you get to the dialog, click the Advanced button and scroll to the bottom of the list to see if the User Instance property is set to True or False.
Here's the issue. Databases that are embedded in VS projects are attached at the time of connection using the AttachDbFile setting in the connection string. In order for this to "just work" VS uses a new type of connection available in SQL Express called RANU (run as normal user) or User Instances. (I'm going to call it RANU 'cause that's easier to type.) The short story is that a RANU Instance has the same permissions that you you have on your computer while the main instances of SQL Express typically runs as the Network Service account. Here is the fun part:
Network Service is a reduced permission account and it doesn't have permission to read files from My Documents, which is where the embedded databases is. No read permissions == can't attach the database. A RANU Instance is running as you, and you do have permissions to your My Documents directory, so RANU == can attach the database.
So here are the alternatives:
- Change the User Instance property to True. (recomended)
- Save the project to a directory that Network Service does have permission to such as something directly under C:
- Give Network Service permissions to your My Documents directory (not recomended)
- Change the service account that is used by the main instances of SQL Express to something other than Network Service.
You can learn more about RANU / User Instances at http://msdn.microsoft.com/sql/express/default.aspx?pull=/library/en-us/dnsse/html/sqlexpuserinst.asp.
Regards,
Mike Wachal - SQL Express team
Well, I hadn't yet created the database, so it couldn't have been embedded in my project in any case 
The problem turned out to be that I wasn't specifying the SQLEXPRESS instance name after my server name. In that regard, my original description of the problem was off a bit—I incorrectly claimed that I was selecting the instance from the combo. In fact, the server name without the instance was what showed up in the selection list.
So, basically, the syntax in #1 now works, while #2 doesn't:
- <server>\SQLEXPRESS
- <server>
Nevertheless, you did bring up some interesting tips, such as the User Instance property and database file locations vs. ACL issues. I'll have to study on those.
Thanks!
SQL Express installs to an Instance Name by default. As you've discovered, you must specify the full instance name, not just the server name, when you connect.
Glad things worked out for you.
Mike
I uninstalled Express and reinstalled Enterprise, so during that particular operation I wasn't able to watch for the instance name issue during setup. Also, the edition of Express that I'd installed came bundled with Visual Studio 2005, which further complicates the question.
I did, however, install Express on another machine, this time directly from the setup file I downloaded from the Express website. During that setup I did indeed encounter a dialog asking me if wanted to accept an instance name of 'SQLExpress' or make the current installation the machine default. I'm already running SQL 2000 on that machine, so I opted for the named instance.
HTH
