ASP and SQLServer 2k Issue

All,

I have scoured all over and I think I have a problem that is a bit more complex. I have a basic 3tier app running on Win2k, SQLServer 2k and asp.net. I set the aspnet account up with access to the db. I have the following code in my form:

<code>

Sub SubmitBtn_Click(ByVal sender As Object, ByVal e As EventArgs) Dim MyConn as SQLConnection= NEw SQLConnection("server=xxx,1433;database=bsld_demo;uid=bsld;password=bsld") Dim MySQL As SQLCommand, TID as string, TParam as SQLParameter MySQL= New SqlCommand("InsertRecord", MyConn) MySQL.CommandType = CommandType.StoredProcedure MySQL.Parameters.Add(New SQLParameter("@.compmodule", compmodule.text)) MySQL.Parameters.Add(New SQLParameter("@.customername", customername.text)) TParam=MySQL.Parameters.Add(New SQLParameter("@.ID", 0)) TParam.Direction=ParameterDirection.Output MyConn.Open() MySQL.ExecuteNonQuery TID=TParam.Value MyConn.Close() End Sub

</code>

When I click on the button I get:

Login failed for user 'bsld'.

I am not sure what to do. The odd thing is on my server I have two instances of sqlserver running. Instance 1 is where I am having the problem. Instance works fine. Anybody run into this before? Thanks

Cordell

It is actually very simple the first thing you do is to register one with the other, by right clicking at the top of Enterprise Manager and registering the instance 1 with the instance. Now to your error it means Asp.net does not have an acount in the instance you are trying to connect to. There are two permissions in SQL Server, the server permission under security in the Management section of Enterprise Manager and the database permission by right click on your database go to all tasks. Then you adjust your Web.Config as needed. In the future it is better to backup and restore so everything including permission is moved so you just adjust the Web.Config. Hope this helps.|||

Sorry for the delay. Here is what I have:

<config file >

<configuration>

<appSettings/><connectionStrings>

<addname="bsld_demoConnectionString"

connectionString="Data Source=server\instance1;Initial Catalog=bsld_demo;Integrated Security=True"

providerName="System.Data.SqlClient"/>

</connectionStrings>

<system.web>

<customErrorsmode="Off"/>

<

authenticationmode="Windows"/>

<!--

</system.web>

</

configuration>

<config/>

<script>

ProtectedSub Button1_Click(ByVal senderAsObject,ByVal eAs System.EventArgs)Dim MyConnAs SqlConnection =New SqlConnection("server=server,1433;Integrated Security=SSPI;database=bsld_demo")Dim MySQLAs SQLCommand, RIDAsString, TParamAs SQLParameter

MySQL =

New SqlCommand("InsertRecord", MyConn)

MySQL.CommandType = CommandType.StoredProcedure

MySQL.Parameters.Add(

New SqlParameter("@.CompModule", CompModule.text))

MySQL.Parameters.Add(

New SqlParameter("@.CustomerName", CustomerName.text))

TParam = MySQL.Parameters.Add(

New SqlParameter("@.ID", 0))

TParam.Direction = ParameterDirection.Output

MyConn.Open()

MySQL.ExecuteNonQuery()

RID = TParam.Value

EndSub

<script/>

The server\ASPNET account has access on the server and to the bsld_demo db. I still get this error:

Exception Details:System.Data.SqlClient.SqlException: Cannot open database requested in login 'bsld_demo'. Login fails.
Login failed for user 'server\ASPNET'.

Source Error:

Line 26: Line 27:Line 28: MyConn.Open()Line 29: Line 30: MySQL.ExecuteNonQuery()

Source File:D:\SOS\ConnectionTest\Default.aspx Line:28

Stack Trace:

[SqlException (0x80131904): Cannot open database requested in login 'bsld_demo'. Login fails.Login failed for user 'server\ASPNET'.] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +735251 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838 System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628 System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +359 System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28 System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424 System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66 System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105 System.Data.SqlClient.SqlConnection.Open() +111 ASP.default_aspx.Button1_Click(Object sender, EventArgs e) in D:\SOS\ConnectionTest\Default.aspx:28 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102

What do I do now? I have tried everything else. Thanks,.|||

You need to go back to my original post about how you create a SQL Server account for Asp.net because of the text below.

(Exception Details: System.Data.SqlClient.SqlException: Cannot open database requested in login 'bsld_demo'. Login fails.
Login failed for user 'server\ASPNET')

And clean up your connection string with the link below. Hope this helps.

http://www.carlprothman.net/Default.aspx?tabid=86

|||Thanks for the suggestions. I cleaned my string. However, I do have a SqlServer account for ASP.Net on the SQLServer. I guess that's why I am confused. In SQLServer I have an account set up as 'server\ASPNET' with server being my server name. I granted that user access to the bsld_demo db.|||

No each instance of SQL Server is a separate server permissions so check the Master database of the instance you are trying to run the application to see if Asp.net account was created in Syslogin table, if it is there try to connect to it if you cannot do that then the permissions is orphaned, that is covered in the second link, it usually involve editing the Master, most places I have worked this require a lot of Management signatures.

http://msdn2.microsoft.com/en-us/library/ms178593.aspx

http://msdn2.microsoft.com/en-us/library/ms175475.aspx

Here is Microsoft covering remote connection see if that makes a difference. Hope this helps.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277

|||

Thanks Caddre. I tried the links and suggestions and nothing worked. I even did the following: I turned Anon access off and tried to log in and got the same "Exception Details:System.Data.SqlClient.SqlException: Cannot open database requested in login 'bsld_demo'. Login fails."
error. This time I got "Login failed for user 'AD\abcxyz'." which AD\abcxyz is my Network ID.

On thin I found interesting is that if I create the connection string in VWD, I can do datagrid functions like edit, delete and insert. But if I try to create a sub procedure and try to link the database that way, this is when I encounter the error. Do you or anyone else know of a way or some examples where I can create a button and code it to do the same thing as the default "Insert" feature in VWD? If I can find this, I'll avoid this issue.

|||

EDIT

I am glad you posted this database was VWD created and that makes it a user instance database, you can fix the problem by backing up the database and restore and it will become regular database. The link below covers it. Post again and let me know how it goes.

http://msdn2.microsoft.com/en-gb/library/ms143684.aspx

|||Hey Caddre. I tried the suggestion in the link and nothing. I thought I would try and create a tbl in another db and point the page to it. I get the same error. I'm almost tempted to reload the .net framework to see if that fixes it but I don't want to screw up the other instance. This is just the craziest thing I have ever seen. You have any other suggestions? You have been awesome in helping just so you know.. :-)|||

EDIT

Try backup and restore first, then the framework and this is from Microsoft support you should change from Windows Authentication to Mixed authentication

http://support.microsoft.com/kb/555332