SQL Connection Error

ZeroFactorial · March 20th, 2009, 11:07 AM

On the completed project, I am able to get to the dashboard.
However when I choose an item from "Request to Approve" or "My Request"

I get a sql connection error:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

From this block of code:

Code:

 
[Function(Name="dbo.ENTAuditObjectSelectByObjectName")]
publicISingleResult<ENTAuditObject> ENTAuditObjectSelectByObjectName([Parameter(Name="ObjectName", DbType="VarChar(255)")] string objectName)
{
IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), objectName);
return ((ISingleResult<ENTAuditObject>)(result.ReturnValue));
}

This is looking for the object PTORequestEO but it is not present becasue the string is empty. I am not able to locate PTORequestEO where is it at in the project?
I have encountered this error before and I believe it was related to the connection string or SQL permissions.

I have already checked to see if my SQL 2005 Server had remote connections turned on in Surface, which it does.
I have also checked that NT Service has access to CRUD rights on the dbo schema.

What has me confused is that I can obviously connect to the db otherwise I would not get to the Home.aspx. Any comments, suggestions are welcomed. Below is my connection string.

Code:

 
<connectionStrings> 
<add name="HRPaidTimeOff" connectionString="Data Source=WEB01TOLLOCBRLW;Initial Catalog=HRPaidTimeOff;Integrated Security=True;Network Library=dbmssocn" providerName=""/> 
</connectionStrings>

varallov · March 20th, 2009, 12:30 PM

Try using a SQL Login instead of integrated security in the connection string. Make sure the SQL Login you create has read, write and execute access to the database.

ZeroFactorial · March 20th, 2009, 12:32 PM

Quote:

Originally Posted by varallov

Try using a SQL Login instead of integrated security in the connection string. Make sure the SQL Login you create has read, write and execute access to the database.

Thanks for the tip. What would the user and pass be?
My Active Directory information or will I need to create it on the db?

varallov · March 20th, 2009, 12:45 PM

It can be anything you want.

1. Open SQL Server Management Studio and connect to your database server.
2. Expand the Security folder and right click on the Logins folder.
3. Select "New Login..."
4. Enter the Login Name. This can be anything you want. Just remember what it is.
5. Select the SQL Server authentication radio button and enter a password.
6. Select User Mappings from the menu on the left side of the dialog box.
7. Check the box next to your database.
8. Check the box next to db_owner for the role membership. This role will allow you to have all the permissions needed in the database.
9. Click the OK button.
10. Change your connection string to use this newly added user name and password.

ZeroFactorial · March 20th, 2009, 03:35 PM

Vince,

thanks for the suggestion. However even with the SQL authentication I am getting the same SQL error on the same line of code in my original post.

I think the message is misleading.

It is looking for the ObjectName field from the ENTAuditObject table.
But this table is completely empty. So the app is unable to return a result. Should this table be populated with data? I used the .sql script from Chpt 12.

I went back and read through Chpt 12 to see if i am over looking something. The only thing I can come up with is that, some of the code expects Auditing to be turned on by default but you have to manually turn this on. For example the block of code from my first post is the stored procedure for ENTAuditObjectSelectedByObjectName.

Code:

ALTER PROCEDURE [dbo].[ENTAuditObjectSelectByObjectName]
(
    @ObjectName varchar(255)
)
AS
    SET NOCOUNT ON

    SELECT ENTAuditObjectId, ObjectName, ObjectFullyQualifiedName, InsertDate, InsertENTUserAccountId, UpdateDate, UpdateENTUserAccountId, Version
      FROM ENTAuditObject
     WHERE ObjectName = @ObjectName

    RETURN

Since the string stored is null, I think this is why the app is throwing this error. The same thing happens whenever i try to Open/Edit any existing request or Submit a new one. It bombs to the original post.

What are your thoughts?

Thanks

ZeroFactorial · March 20th, 2009, 05:52 PM

Ok figured out the problem. :)

What I did was trace through, code and I found a block of code in DBHelper.cs

Code:

namespace V2.PaidTimeOffDAL.Framework
{
    public class DBHelper
    {
        private const string HRPAIDTIMEOFF_CONNSTRING_KEY = "HRPaidTimeOff";

        public static string GetHRPaidTimeOffConnectionString()
        {
            return ConfigurationManager.ConnectionStrings[HRPAIDTIMEOFF_CONNSTRING_KEY].ConnectionString;
        }

    }
}

Where HRPAIDTIMEOFF_CONNSTRING_KEY = "HRPaidTimeOff" was "HRPaidTimeOffConnectionString"

From here I traced to V2.PaidTimeOffDAL.Properties.Settings
and discovered that the connection string stored in here is still defaulted to the Varallo1 datasource with V2Application for the user.

After making the changes to this I compiled and everything seems working as it should. No more SQL Error 40. Yay!

Any additional info on the V2.PaidTimeOffDAL.Properties.Settings file and its operation will prove helpful to me and maybe others. How do we update all of these areas at once if possible?

Thanks again