Link to home
Start Free TrialLog in
Avatar of Ann K
Ann K

asked on

Connection String

Can you tell me how to resolve this error?
User generated imageUser generated image
EmployeeBusinessLyaer.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace BusinessLayer
{
    public class EmployeeBusinessLayer
    {
        public IEnumerable<Employee> Employees
        {
            get
            {
                string connectionString =
                    ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;

                List<Employee> employees = new List<Employee>();

                using (SqlConnection con = new SqlConnection(connectionString))
                {
                    SqlCommand cmd = new SqlCommand("spGetAllEmployees", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    con.Open();
                    SqlDataReader rdr = cmd.ExecuteReader();
                    while (rdr.Read())
                    {
                        Employee employee = new Employee();
                        employee.ID = Convert.ToInt32(rdr["Id"]);
                        employee.Name = rdr["Name"].ToString();
                        employee.Gender = rdr["Gender"].ToString();
                        employee.City = rdr["City"].ToString();
                        employee.DateOfBirth = Convert.ToDateTime(rdr["DateOfBirth"]);

                        employees.Add(employee);
                    }
                }

                return employees;
            }
        }
    }
}

Open in new window

System.Data.SqlClient.SqlException was unhandled by user code
  HResult=-2146232060
  Message=Login failed for user 'IIS APPPOOL\ASP.NET V4.0 Integrated'.
  Source=.Net SqlClient Data Provider
  ErrorCode=-2146232060
  Class=14
  LineNumber=65536
  Number=18456
  Procedure=""
  Server=.
  State=1
  StackTrace:
       at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling)
       at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
       at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
       at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
       at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
       at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
       at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
       at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
       at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
       at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
       at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
       at System.Data.SqlClient.SqlConnection.Open()
       at BusinessLayer.EmployeeBusinessLayer.get_Employees() in C:\Users\User\documents\visual studio 2010\Projects\MVCDemo\BusinessLayer\EmployeeBusinessLayer.cs:line 26
       at MVCDemo.Controllers.EmployeeController.Index() in C:\Users\User\documents\visual studio 2010\Projects\MVCDemo\MVCDemo\Controllers\EmployeeController.cs:line 17
       at lambda_method(Closure , ControllerBase , Object[] )
       at System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary`2 parameters)
       at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters)
       at System.Web.Mvc.Async.AsyncControllerActionInvoker.<>c__DisplayClass42.<BeginInvokeSynchronousActionMethod>b__41()
       at System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethod(IAsyncResult asyncResult)
       at System.Web.Mvc.Async.AsyncControllerActionInvoker.<>c__DisplayClass37.<>c__DisplayClass39.<BeginInvokeActionMethodWithFilters>b__33()
       at System.Web.Mvc.Async.AsyncControllerActionInvoker.<>c__DisplayClass4f.<InvokeActionMethodFilterAsynchronously>b__49()
  InnerException: 

Open in new window

Avatar of Shaun Vermaak
Shaun Vermaak
Flag of Australia image

You need to specify a username and password in the connection string, otherwise it will use the App Pool user

https://www.connectionstrings.com/
Avatar of Ann K
Ann K

ASKER

In the web.config file?
<connectionStrings>
    <add name="DBCS"
          connectionString="server=.; database=Sample; integrated security=SSPI"
          providerName="System.Data.SqlClient"/>
  </connectionStrings>

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Shaun Vermaak
Shaun Vermaak
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Ann K

ASKER

Looks like similar to that one but what I will put in user id and password as I use windows authentication.

User generated image
Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;
User ID=myDomain\myUsername;Password=myPassword;

Open in new window

Avatar of Ann K

ASKER

I tried it like this but still same error.

connectionString="server=.; database=Sample; integrated security=SSPI;User ID=HARRIS-PC;Password=;"

Open in new window

Try removing integrated security=SSP
Avatar of Ann K

ASKER

It still going on the same line.
con.Open()
Did you give the user SQL rights? Does the error now show Harris as the user?
Avatar of Ann K

ASKER

I can't find "Harris" any of these files.
User generated imageUser generated image
System.Data.SqlClient.SqlException was unhandled by user code
  HResult=-2146232060
  Message=Login failed for user ''.
  Source=.Net SqlClient Data Provider
  ErrorCode=-2146232060
  Class=14
  LineNumber=65536
  Number=18456
  Procedure=""
  Server=.
  State=1
  StackTrace:
       at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling)
       at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
       at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
       at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
       at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
       at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
       at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
       at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
       at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
       at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
       at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
       at System.Data.SqlClient.SqlConnection.Open()
       at BusinessLayer.EmployeeBusinessLayer.get_Employees() in C:\Users\User\Documents\Visual Studio 2010\Projects\MVCDemo\BusinessLayer\EmployeeBusinessLayer.cs:line 26
       at MVCDemo.Controllers.EmployeeController.Index() in C:\Users\User\Documents\Visual Studio 2010\Projects\MVCDemo\MVCDemo\Controllers\EmployeeController.cs:line 17
       at lambda_method(Closure , ControllerBase , Object[] )
       at System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary`2 parameters)
       at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters)
       at System.Web.Mvc.Async.AsyncControllerActionInvoker.<>c__DisplayClass42.<BeginInvokeSynchronousActionMethod>b__41()
       at System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethod(IAsyncResult asyncResult)
       at System.Web.Mvc.Async.AsyncControllerActionInvoker.<>c__DisplayClass37.<>c__DisplayClass39.<BeginInvokeActionMethodWithFilters>b__33()
       at System.Web.Mvc.Async.AsyncControllerActionInvoker.<>c__DisplayClass4f.<InvokeActionMethodFilterAsynchronously>b__49()
  InnerException: 

Open in new window

Avatar of Ann K

ASKER

>Did you give the user SQL rights?

How to i do that? Both SQL Server 2014 database and the vs 2010 are installed on the same machine.
"Persist Security Info=False;User ID=*****;Password=*****;Initial Catalog=AdventureWorks;Server=MySqlServer"
Avatar of Ann K

ASKER

Getting syntax error.
User generated image
  <connectionStrings>
    <add name="DBCS"
connectionString="server=.; database=Sample;"Persist Security Info=False;User ID=*****;Password=*****;Initial Catalog=AdventureWorks;Server=MySqlServer"
          providerName="System.Data.SqlClient"/>
  </connectionStrings>

Open in new window

connectionString="Persist Security Info=False;User ID=TypeInYourUser;Password=TypeInYouPassword;Initial Catalog=Sample;Server=."
Avatar of Ann K

ASKER

What i put in the user name
User generated image
Avatar of Ann K

ASKER

I tried this but getting error. I am using windows authentication. I don't know the user id and password.
          connectionString="Persist Security Info=False;User ID=sa;Password='';Initial Catalog=Sample;Server=."

Open in new window

User ID=sa;Password=''; That is NOT Windows Authentication but Sql Server Authentication, Windows Authentication uses computername\username