标题大致描述了这个问题。我正在尝试使用角色授权，并且已成功创建用户和角色。我还可以为用户分配角色。我已经验证了以下操作确实修改了数据库表。但是，当我在控制器方法上使用角色授权时，我遇到了某种SQL Server超时。
例如: [Authorize(Roles="Admin")]
public ActionResult Admin()
{
ViewBag.Message = "Your admin page.";
var context = new IdentityDbContext();
IDbSet<IdentityRole> roles = context.Roles;
List<string> roleList = new List<string>();
foreach (IdentityRole role in roles)
roleList.Add(role.Name);
ViewBag.Roles = roleList;
IDbSet<IdentityUser> users = context.Users;
List<string> userList = new List<string>();
foreach (IdentityUser user in users)
userList.Add(user.UserName);
ViewBag.Users = userList;
return View();
}
但是这将超时，响应:
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
任何帮助都将非常感激。您可以在下面找到我的SQL连接字符串，以及创建角色和向角色添加用户的方法。谢谢你
SQL Connection String:
<connectionStrings>
<add name="DefaultConnection" connectionString="Data Source=192.168.10.160,1433;Initial Catalog=PriceConfigurator;User Id=PriceConfiguratorDBA;Password=*********" providerName="System.Data.SqlClient" />
</connectionStrings>
角色/用户管理:
[HttpPost]
public ActionResult AddToUser(FormCollection collection)
{
try
{
var roleManager = new RoleManager<Microsoft.AspNet.Identity.EntityFramework.IdentityRole>(new RoleStore<IdentityRole>(new ApplicationDbContext()));
var userManager = new UserManager<Microsoft.AspNet.Identity.EntityFramework.IdentityUser>(new UserStore<IdentityUser>(new ApplicationDbContext()));
var context = new IdentityDbContext();
var userName = collection["AddUserRoleUsername"];
IdentityUser user = context.Users.Where(x => x.UserName == userName).FirstOrDefault();
var roleName = collection["AddUserRoleNameList"];
userManager.AddToRole(user.Id, roleName);
return Json(userName + " added to " + roleName, JsonRequestBehavior.DenyGet);
}
catch (Exception ex)
{
return Json("Error occured: " + ex.Message, JsonRequestBehavior.DenyGet);
}
}
[HttpPost]
public ActionResult RemoveFromUser(FormCollection collection)
{
try
{
var roleManager = new RoleManager<Microsoft.AspNet.Identity.EntityFramework.IdentityRole>(new RoleStore<IdentityRole>(new ApplicationDbContext()));
var userManager = new UserManager<Microsoft.AspNet.Identity.EntityFramework.IdentityUser>(new UserStore<IdentityUser>(new ApplicationDbContext()));
var context = new IdentityDbContext();
var userName = collection["RemoveUserRoleUsername"];
IdentityUser user = context.Users.Where(x => x.UserName == userName).FirstOrDefault();
var roleName = collection["RemoveUserRoleNameList"];
userManager.RemoveFromRole(user.Id, roleName);
return Json(userName + " removed from " + roleName, JsonRequestBehavior.DenyGet);
}
catch (Exception ex)
{
return Json("Error occured: " + ex.Message, JsonRequestBehavior.DenyGet);
}
}
我也尝试在我的web.config
中覆盖会员提供程序 <system.web>
<compilation debug="true" targetFramework="4.5" />
<httpRuntime targetFramework="4.5" />
<membership>
<providers>
<clear/>
<add name="AspNetSqlRoleProvider" connectionStringName="DefaultConnection" applicationName="/" type="System.Web.Security.SqlRoleProvider"/>
<add name="AspNetSqlProfileProvider" connectionStringName="DefaultConnection" applicationName="/" type="System.Web.Profile.SqlProfileProvider, System.Web, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
<add name="AspNetSqlMembershipProvider" type="System.Web.Security.SqlMembershipProvider, System.Web, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" connectionStringName="DefaultConnection" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="true" applicationName="/" requiresUniqueEmail="false" passwordFormat="Hashed" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="7" minRequiredNonalphanumericCharacters="1" passwordAttemptWindow="10" passwordStrengthRegularExpression="" />
</providers>
</membership>
<roleManager enabled="true">
</roleManager>
以下是一些需要检查的内容:
Make sure your database engine is configured to accept remote connections:
Start > All Programs > SQL Server 2005 > Configuration Tools > SQL Server Surface Area Configuration
Click on Surface Area Configuration for Services and Connections
Select the instance that is having a problem > Database Engine > Remote Connections
Enable local and remote connections
Restart instance
你也可以检查这个链接，以解决"在建立到SQL Server的连接时发生了与网络相关或特定实例的错误…"
You may need to create an exception on the firewall for the SQL Server instance and port you are using:
Start > Run > Firewall.cpl
Click on exceptions tab
Add sqlservr.exe
typically located in C:Program Files (x86)Microsoft SQL ServerMSSQL.xMSSQLBin - check your installs for the actual folder path
Add the port (default is 1433)
Check your connection string as well
Check if your SQL server services is up and running properly:
Go to All Programs > Microsoft SQL Server 2008 > Configuration Tools > SQL Server Configuration Manager > SQL Server Services
Check to make sure SQL Server service status is Running.
Ensure that your remote server is in the same network:
Open your command prompt and Run sqlcmd -L to ascertain if your server is included in your network list.
See More At: [Using sqlcmd – Detect Installed SQL Server on Network][1]
Enable TCP/IP in SQL Server Configuration:
Go to All Programs > Microsoft SQL Server 2008 > Configuration Tools > SQL Server Configuration Manager > Select TCP/IP.
Right click and select enable
您也可以尝试此链接fix-error-provider-named-pipes-provider-error-40-could-not-open-a-connection-to-sql-server-microsoft-sql-server-error