Why does this code cause an error?

2
votes

I have been looking for hours on how to connect to my local SQL database. I can't believe its been this difficult. Anyway, i think I am more on the right track with this code.

<%
    Dim myConnection as System.Data.SqlClient.SqlConnection
    Dim myCommand as System.Data.SqlClient.SqlCommand

    myConnection = New System.Data.SqlClient.SqlConnection("Data Source=localhost;Initial Catalog=dbtest;Integrated Security=True")
    myConnection.Open()
%>

However, It still does not work. I don't know where to begin because I also get this message when the page loads:

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 tag within a "web.config" configuration file located in the root directory of the current web application. This tag should then have its "mode" attribute set to "Off".

I created a web.config file, and it didn't change anything. I'm so lost. Any help is appreciated!

Stack Trace: [SqlException (0x80131904): Cannot open database "dbtest" requested by the login. The login failed. Login failed for user 'IIS APPPOOL\DefaultAppPool'.]
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +6244425 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +245
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2811
System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +53
System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) +248
System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) +6260362 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +6260328 System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +354
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +703
System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +54 System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +6261592
System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +81
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +1657
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +88
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +6265031
System.Data.SqlClient.SqlConnection.Open() +258 ASP.index_aspx.__Render__control1(HtmlTextWriter __w, Control parameterContainer) +71 System.Web.UI.Control.RenderChildrenInternal(HtmlTextWriter writer, ICollection children) +115
System.Web.UI.Page.Render(HtmlTextWriter writer) +38
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +4240

4
asp.netado.netconnectionsqlclient

4 Answers

7
votes

It's here

Cannot open database "dbtest" requested by the login. The login failed. Login failed for user 'IIS APPPOOL\DefaultAppPool'

The App Pool identity account is set up in SQL Server but is not set up in the database dbtest. You get a different error message if you could not connect to the SQL Server instance, but you are connecting for you to get this error message above.1

So, in SQL:

USE dbtest
GO
CREATE USER [IIS APPPOOL\DefaultAppPool] FROM LOGIN [IIS APPPOOL\DefaultAppPool]

Edit:

When you connect to a "database", you actually authenticate 3 times (basically)

  1. To the Windows/domain install hosting the SQL Server Instance (out of scope here)
  2. To the SQL Server instance, where you are a "login" set up SQL Server via CREATE LOGIN
  3. This login maps to zero or more databases as a "user", which is what you needed to do here

In this case, you were set up in SQL Server as a login but not set up in the target database hosted within the SQL Server instance.

1
votes

Put the CustomError tag and value properly, like: 

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

This will expose the exact issue to you.

0
votes

Try to set Data Source=(local)

0
votes

Have you followed all the instructions and made sure the customerErrors tag in the web.config looks like this:

<customErrors mode="Off" />

And the real error appears to be that the user ASP.Net is running as does not have permission to log in to the database.