Can't connect to local SQL Server DB with connection string, but VS can

4
votes

I'm trying to connect to a local Sql Server 2012 database instance trough Visual Studio 15 using connection string. While I've been able to connect to the DB through the Visual Studio SQL Server Object Explorer and run a test procedure successfully, I'm unable to do so in my project using connection string.

Here's my connection string that was given by the SQL Server Object Explorer 

<connectionStrings>    
  <add name="objConnLocal" 
connectionString="Data Source=DESKTOPIVO;Integrated Security=True;Initial Catalog=tnk;Connect Timeout=15;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False;"providerName="System.Data.SqlClient"/>
</connectionStrings>

Here's my code that works perfectly fine connecting to an Azure DB with Azure given connection string (the code works)

public class DbConn
{

public static string DatabaseConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["objConnLocal"].ConnectionString;
    public static SqlConnection objConn = new sqlConnection(DatabaseConnectionString);



public void spCMSSelectTest(out DataSet ds)
{
    ds = new DataSet("buttons");
    SqlDataAdapter objDataAdapter = new SqlDataAdapter();
    SqlCommand cmd = new SqlCommand("spCMSSelectTest", DbConn.objConn);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Connection.Open();
    objDataAdapter.SelectCommand = cmd;
    objDataAdapter.Fill(ds);
    cmd.Connection.Close();
}
}

And this is the DB I'm trying to connect to: enter image description here

Here's the test Page I'm trying to access:

 protected void Page_Load(object sender, EventArgs e)
{
    DataSet ds;
    try
    { 
    dbc.spCMSSelectTest(out ds);

        lblTest.Text = ds.Tables[0].Rows.Count.ToString();
    }
    catch (Exception easd)
    {
        lblTest.Text = easd.ToString();
    }

}

I've been going trough answers here and on google but nothing seemed to work for me. What Am I missing?

EDIT: There's the error:

System.Data.SqlClient.SqlException (0x80131904): Login failed for user 'IIS APPPOOL\DefaultAppPool'. 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, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource1 retry) at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry) at System.Data.SqlClient.SqlConnection.Open() at DbConn.spCMSSelectTest(DataSet& ds) in c:\inetpub\wwwroot\CMS\App_Code\DbConn.cs:line 22 at index.Page_Load(Object sender, EventArgs e) in c:\inetpub\wwwroot\CMS\index2.aspx.cs:line 18 ClientConnectionId:d576fae4-db3d-451d-8436-2abe003c01f0 Error Number:18456,State:1,Class:14

2
c#sql-servervisual-studioconnection-string
What error do you get on connection? – BugFinder
that doesnt sound like a sql connection issue that sounds like an iis issue – BugFinder
but you just said it didnt error.... what is the error – BugFinder
What does openConnection do? Im guessing something is erroring - but your code isnt reporting it back – BugFinder
Well there you go, you're trying to log in with trusted connection but its running in a default pool not as a username... which apparently has no rights to log in to sql - you need to log in as "you" or "an sql user" – BugFinder

2 Answers

3
votes

You're logging in as a trusted user (default) - and that user is the pool user IIS APPPOOL\DefaultAppPool you need to either login as you, or an SQL login. As its not using the same credentials when running under a pool as it is from your desktop.

1
votes

You're missing the database.

Add Initial Catalog=tnk to your connection string.