SQL Server Integrated Authentication

[David]

Will a connection string using Integrated Security in the web.config file work? I tried building a connection string but wasn't successful in getting the Web Console to run using the connection string. Is there a certain format that your application expects to see in the config file?

[David]

The connection string, modified as you suggested, does work if I take additional steps when using IIS 6.

Connection String (With SQL being on the same server, substitute 127.0.0.1 with another IP or server name for remote DB)

"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Lansweeper;Data Source=127.0.0.1"

Here's the additional steps I took:

1. Configured the web site to use Integrated Authentication only
2. Added a local user account to the web server
2. Added the user account to the IIS_WPG group on the web server
3. Removed the local users account from the web site permissions
4. Created an Application Pool
5. Configured the Application Pool to run using the new user account as its Identity
6. Assigned the Application Pool to the web site on the Home Directory of the web site properties page
7. Granted Read, Execute and List permissions to the IIS_WPG for the web site
8. Added the user account to the SQL Logins
9. Granted SQL permissions for the user account to access the Lansweeper database and to execute all of the "web" stored procedures.

As a member of the local administrators group on the web server I don't need to do anything else to access the web content but, to give other users access to the web site, another group could be created and then given Read, Execute and List permissions on the web site. Adding users to that group would then give those users access to the web content as well.

Thanks, your responses gave me new insight into the problem and helped me figure out what I needed to do.

[Hemoco]

Login failed for user 'NT AUTHORITY\NETWORK SERVICE' means you are using this user and this user has no access.

Maybe it's an easier solution to just put NT security on the website and use a username and password in the sqlconnectionstring
This way your webpages will still be protected

[David]

When I drop "Provider=SQLOLEDB.1;" from the connection string, I get this error message.

System.Data.SqlClient.SqlException: Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.
at System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction)
at System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction)
at System.Data.SqlClient.SqlConnection.Open()
at DreamweaverCtrls.DataSet.DoInit()
--------------------------------------------------------------------------------
System.Data.SqlClient.SqlException: Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.
at System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction)
at System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction)
at System.Data.SqlClient.SqlConnection.Open()
at DreamweaverCtrls.DataSet.DoInit()
--------------------------------------------------------------------------------
System.Exception: The DefaultView was requested but no tables yet exist.
at DreamweaverCtrls.DataSet.get_DefaultView()
--------------------------------------------------------------------------------
System.Data.SqlClient.SqlException: Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.
at System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction)
at System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction)
at System.Data.SqlClient.SqlConnection.Open()
at DreamweaverCtrls.DataSet.DoInit()
--------------------------------------------------------------------------------
System.Exception: The DefaultView was requested but no tables yet exist.
at DreamweaverCtrls.DataSet.get_DefaultView()
--------------------------------------------------------------------------------

[David]

Looks like the web content was built with Dreamweaver. The integrated security connection string would probably need to be used to build the database connection file that is generated by Dreamweaver. I'm no web developer but I have had to work with web sites and database connections built with Dreamweaver. I remember there being a considerable amount of maintenance if there were multiple DB connections using user name and password instead of a generic connection string.

If this is the problem, maybe you could package the application with an option depending upon the way the user would like to configure access to the database. You might be able to include different versions of the MMHTTPDB files.

[Hemoco]

Drop the "Provider=SQLOLEDB.1;" in your connectionstring and it should work fine

[David]

Here is the error that I get on the server; it appears in the web frame that should display the list of computers. At the end of the post is the config file that was in use to generate this error.

System.ArgumentException: Keyword not supported: 'provider'.
at System.Data.Common.DBConnectionString.ParseInternal(Char[] connectionString, UdlSupport checkForUdl, NameValuePair& keychain)
at System.Data.Common.DBConnectionString..ctor(String connectionString, UdlSupport checkForUdl)
at System.Data.SqlClient.SqlConnectionString..ctor(String connectionString)
at System.Data.SqlClient.SqlConnectionString.ParseString(String connectionString)
at System.Data.SqlClient.SqlConnection.set_ConnectionString(String value)
at System.Data.SqlClient.SqlConnection..ctor(String connectionString)
at DreamweaverCtrls.DataSet.NewDbConnection(String strConnection)
at DreamweaverCtrls.DataSet.DoInit()
--------------------------------------------------------------------------------
System.ArgumentException: Keyword not supported: 'provider'.
at System.Data.Common.DBConnectionString.ParseInternal(Char[] connectionString, UdlSupport checkForUdl, NameValuePair& keychain)
at System.Data.Common.DBConnectionString..ctor(String connectionString, UdlSupport checkForUdl)
at System.Data.SqlClient.SqlConnectionString..ctor(String connectionString)
at System.Data.SqlClient.SqlConnectionString.ParseString(String connectionString)
at System.Data.SqlClient.SqlConnection.set_ConnectionString(String value)
at System.Data.SqlClient.SqlConnection..ctor(String connectionString)
at DreamweaverCtrls.DataSet.NewDbConnection(String strConnection)
at DreamweaverCtrls.DataSet.DoInit()
--------------------------------------------------------------------------------
System.Exception: The DefaultView was requested but no tables yet exist.
at DreamweaverCtrls.DataSet.get_DefaultView()
--------------------------------------------------------------------------------
System.ArgumentException: Keyword not supported: 'provider'.
at System.Data.Common.DBConnectionString.ParseInternal(Char[] connectionString, UdlSupport checkForUdl, NameValuePair& keychain)
at System.Data.Common.DBConnectionString..ctor(String connectionString, UdlSupport checkForUdl)
at System.Data.SqlClient.SqlConnectionString..ctor(String connectionString)
at System.Data.SqlClient.SqlConnectionString.ParseString(String connectionString)
at System.Data.SqlClient.SqlConnection.set_ConnectionString(String value)
at System.Data.SqlClient.SqlConnection..ctor(String connectionString)
at DreamweaverCtrls.DataSet.NewDbConnection(String strConnection)
at DreamweaverCtrls.DataSet.DoInit()
--------------------------------------------------------------------------------
System.Exception: The DefaultView was requested but no tables yet exist.
at DreamweaverCtrls.DataSet.get_DefaultView()
--------------------------------------------------------------------------------


Contents of the web.config file.

<configuration>
<appSettings>
<add key="MM_CONNECTION_HANDLER_Lansweeper" value="sqlserver.htm" />
<add key="MM_CONNECTION_STRING_Lansweeper" value="Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Lansweeper;Data Source=127.0.0.1" />
<add key="MM_CONNECTION_DATABASETYPE_Lansweeper" value="SQLServer" />
<add key="MM_CONNECTION_SCHEMA_Lansweeper" value="" />
<add key="MM_CONNECTION_CATALOG_Lansweeper" value="" />
</appSettings>
</configuration>

[David]

I get the same error if I remove the local users group from the web site permissions. That may be the key; it appears that the code is dependant upon the local users group for access to the web content.

[Hemoco]

Could you try the same from the server's website, this will show the correct error.
Could you also post your config file please.

[David]

No, I made sure that I turned off anonymous access and configured only Integrated Authentication. I am a domain administrator so I have, by default, membership in the local administrators group on the server that is hosting the web services and the SQL DB.

Server Error in '/' Application.
--------------------------------------------------------------------------------

Runtime Error
Description: An application error occurred on the server. The current custom error settings for this application prevent the details of the application error from being viewed remotely (for security reasons). It could, however, be viewed by browsers running on the local server machine.

Details: To enable the details of this specific error message to be viewable on remote machines, please create a <customErrors> tag within a "web.config" configuration file located in the root directory of the current web application. This <customErrors> tag should then have its "mode" attribute set to "Off".


<!-- Web.Config Configuration File -->

<configuration>
<system.web>
<customErrors mode="Off"/>
</system.web>
</configuration>


Notes: The current error page you are seeing can be replaced by a custom error page by modifying the "defaultRedirect" attribute of the application's <customErrors> configuration tag to point to a custom error page URL.


<!-- Web.Config Configuration File -->

<configuration>
<system.web>
<customErrors mode="RemoteOnly" defaultRedirect="mycustompage.htm"/>
</system.web>
</configuration>