3

I am trying to get authentication and authorization working with my ASP MVC project. I've run the aspnet_regsql.exe tool without any problem and see the aspnetdb database on my server (using the Management Studio tool).

my connection string in my web.config is:

 <connectionStrings>
 <add name="ApplicationServices"
     connectionString="data source=MYSERVERNAME;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true"
     providerName="System.Data.SqlClient" />
 </connectionStrings>

The error I get is:

There is a problem with your selected data store. This can be caused by an invalid server name or credentials, or by insufficient permission. It can also be caused by the role manager feature not being enabled. Click the button below to be redirected to a page where you can choose a new data store.

The following message may help in diagnosing the problem: Unable to connect to SQL Server database.

In the past, I have had trouble connecting to my database because I've needed to add users. Do I have to do something similar here?

Edit Changing the connection string ultimately solved the problem. For the records I am using VS2010, ASP MVC2, SServer 2008

<connectionStrings>
<add name="ApplicationServices"
     connectionString="data source=MYSERVERNAME;Integrated Security=True;Initial Catalog=aspnetdb"
     providerName="System.Data.SqlClient" />
</connectionStrings>
Community
  • 1
  • 1
MedicineMan
  • 15,008
  • 32
  • 101
  • 146
  • I wonder if this article will help: http://imar.spaanjaars.com/395/configuring-an-aspnet-20-application-to-work-with-microsoft-sql-server-2000-or-sql-server-2005 – MedicineMan Jul 20 '10 at 06:09

2 Answers2

1

You are using a server name, "MYSERVER" as if this is a full SQl Server Default instance, not Sql Express. I don't think you can use the AttachDbFilename with a full blown sql server. Either add "\SQLEXPRESS" (instance name) to your server name or get rid of the AttachDbFileName and use Database="NAMEOFDATABASE"

matt-dot-net
  • 4,204
  • 21
  • 24
  • you are correct, I am using full SQL Server. When I get home later tonight, I'll have a look at your suggestion. – MedicineMan Jul 19 '10 at 15:05
  • added info: use Database="NAMEOFDATABASE" or Initial Catalog="NAMEOFDATABASE" – matt-dot-net Jul 19 '10 at 15:20
  • I tried this and it did not resolve the issue. I got the following error: The user instance login flag is not supported on this version of SQL Server. The connection will be closed. – MedicineMan Jul 21 '10 at 06:06
  • The error means that ASP.Net is trying to create a database for you automatically. This is called, "User Instancing" and is not supported on the full blown version of SQLServer 2005. It only works with SQL Express. You need to create the database and run aspnet_regsql.exe to add the membership and role features. – matt-dot-net Jul 21 '10 at 12:58
0

In my experience, which may be using a slightly different scenario, the ASPNET service account (Machine_X\ASPNET or Network Service) requries access granting to the source db, like so if it doesn't already have it:

-- Grant ASPNET access to the database
USE [##DBNAME##]
GO 

-- Grant login to Network service for ASPNET membership
EXEC sp_grantlogin N'##SERVICEACCOUNT##'
go

-- Grant minimum permissions
USE [##DBNAME##]
GO 
sp_addrolemember 'aspnet_Membership_FullAccess', N'##SERVICEACCOUNT##'
GO
Tanner
  • 22,205
  • 9
  • 65
  • 83
  • Does the ASPNET service account already exist, or do I have to create it? -- how do I figure out what is the service account? – MedicineMan Jul 19 '10 at 15:06
  • Yes, it's a machine account so it will exist. Depending on OS, you will either use MACHINE\ASPNET (Win XP) or NETWORK SERVICE (vista+ or win server) as the default aspnet account, or you can look at creating a service account: http://msdn.microsoft.com/en-us/library/ff649309.aspx – Tanner Jul 20 '10 at 17:53
  • I am getting the same error in the ASP.NET Web Site Administration Tool, which runs on the VS ASP.NET Development Server (not IIS, not IIS Express). How do I find out the identity that is running ASP.NET on this VS Web Server? – Old Geezer Oct 06 '12 at 15:30