Security Security Trimming SQL Queries

Question

I use the default Sitemap provider with timing disabled . But somehow, I get:

When connecting to SQL Server, a network-related or specific instance error occurred.

I think the sitemap provider is looking for roles in the wrong place. My configuration is as follows:

<connectionStrings> <add name="DB" ... /> </connectionStrings> <membership defaultProvider="SqlProvider" userIsOnlineTimeWindow="15"> <providers> <clear/> <add name="SqlProvider" .../> </providers> </membership> <roleManager enabled="true"> <providers> <add connectionStringName="DB" type="System.Web.Security.SqlRoleProvider" ... /> </providers> </roleManager> 

The Sitemap tag is listed as follows:

 <siteMap defaultProvider="XmlSiteMapProvider" enabled="true" > <providers> <clear/> <add name="XmlSiteMapProvider" description="Default SiteMap provider." type="System.Web.XmlSiteMapProvider " siteMapFile="Web.sitemap" securityTrimmingEnabled="true" /> </providers> </siteMap> 

Why am I getting sql error? How does cropping get roles?

EDIT:

Answer 1

The main mistake of your screenshot is

An error occurred with the network or a specific instance while establishing a connection to SQL Server. The server was not found or was unavailable. Verify Instance Name and SQL Server

Unfortunately, this is truncated. Typically, the message will continue with "configured to allow remote connections." This may be followed by a specific reason (for example, it may indicate (among other reasons) "provider: SQL Network Interfaces, error: 26 - Server / instance location error")

However, with the message you still have, it looks like a connection problem between the client machine and the SQL Server field. Therefore, I would look at the need for due diligence in front of the SQL Server field and network connection:

• SQL Server name may resolve
• So the server can be reached with ping
• This SQL server is configured to accept remote connections
• That the SQL Browser service is running in the SQL Server box
• What Windows Firewall does not interfere with
• SQL Server itself actually works first

If they pass, you need to confirm that you can log in. I usually use SQL management tools, especially the sqlcmd command-line sqlcmd to check for basic communication (for example: sqlcmd -E -S mysqlserver\instance to connect to the default database or sqlcmd -E -S mysqlserver\instance -d database database). Obviously, you will need to run them as the user your web application is working with, otherwise they will try to authenticate as you (either use runas , or run the command line under different credentials [find the command line at the beginning then press shift- right-click → "Run as another user".

However, that ultimately helps, if you can get the full text of the error message (and not the truncated version), then this can help narrow down the specific problem you are facing.

Answer 2

Try this working site map:

 <siteMap defaultProvider="XmlSiteMapProvider" enabled="true"> <providers> <add name="XmlSiteMapProvider" description="SiteMap provider which reads in .sitemap XML files." type="System.Web.XmlSiteMapProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" siteMapFile="web.sitemap" securityTrimmingEnabled="true" /> </providers> </siteMap> 

Answer 3

I would try to create a completely new project using only the default role provider and security trim, and see if you can do this.

Answer 4

If SqlRoleProvider works without security trimming enabled, I assume that there will be something in your database or connection string that will prevent multiple concurrent connections. It would be much easier to evaluate if you could provide information about your DB connection string ...

Answer 5

In general, the configuration looks correct. According to the exception message, the database is not available. Have you checked if you can connect the database?

 using (SqlConnection connection = new SqlConnection()) { connection.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["DB"].ConnectionString; try { connection.Open(); } catch() { ... } } 

"Steps to troubleshoot SQL connectivity issues"