I have tried to connect to my SQL database being hosted on Plesk with InterServer using C# ASP.NET MVC. Despite numerous efforts, it never seems to want to connect. What am I doing wrong?
I've tried changing the connection string numerous times. Plesk and InterServer support ask me to deploy my code and access the database via localhost. Due to this, I have tried the following connection strings:
"SERVER=localhost;DATABASE=databasename;UID=username;PASSWORD=password;"
"SERVER=localhost:3306;DATABASE=databasename;UID=username;PASSWORD=password;"
"SERVER=localhost;PORT=3306;DATABASE=databasename;UID=username;PASSWORD=password;"
"SERVER=(Local);DATABASE=databasename;UID=username;PASSWORD=password;"
I have tried interchanging "Server" with "Data Source", among other keyword changes. I have also tried setting Trusted_Connection to both true and false, and setting Integrated Security to both true and false. It seems when I try to set them to true, the application tries to log in using my Plesk username rather than the User ID I specified in the connection string. This user has no association to the database nor am I able to associate it in the Plesk control panel to my knowledge.
I have also tried using the default connection string in the Plesk Control Panel alongside a couple of modifications to said connection strings, one of which even provided by InterServer support as they suspect the error is in my connection strings. Here are those strings:
"data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true"
"Data Source=(IPAddressWasHere)\MSSQLSERVER2017;Initial Catalog=nlstatic;Persist Security Info=True;User ID=username;Password=password"
"Data Source=.\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\db.mdf;Initial Catalog=Portal.Web;Integrated Security=True"
"Data Source=.\SQLExpress;Initial Catalog=localhost;Integrated Security=True"
I have also tried changing the proverName from System.Data.SqlClient to MySql.Data.SqlClient, but this seemed to not change the error message. Personally, I'm at the point where I suspect that this issue isn't with a connection string setup, but instead with the configuration of the MySQL database or its settings.
Despite trying to ask InterServer support for assistance regarding this, they refuse to look into the matter. Instead, they once asked me to "properly set up my VPS" (I don't have a VPS with them at all) and tried to convince me I was trying to connect via ODBC when I clearly stated it was through connection strings.
In case it's relevant, here's my C# code on how I'm using the connection string, though I don't feel it's the cause personally:
public List<string> GetEffectByID(int id)
{
List<string> results = new List<string>();
SqlConnection conn;
string connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
conn = new SqlConnection(connectionString);
try
{
using (SqlCommand command = new SqlCommand("getFxById", conn) { CommandType = CommandType.StoredProcedure })
{
conn.Open();
using (SqlDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
results.Add(dr["myColumn"].ToString());
}
}
conn.Close();
return results;
}
} catch(Exception e)
{
if(conn.State == ConnectionState.Open) conn.Close();
return new List<string>() { e.ToString() };
}
}
This stored procedure definitely exists in my database, and it works in phpMyAdmin
Is there something that I've missed throughout this entire process? I expect it to be able to connect and run the stored procedure when my View calls the function. Despite this, using the methods provided by Plesk/InterServer, it claims that the server was not found/accessible. When I try to use the connection strings I've created (Without using Integrated Security), it provides this error message instead:
System.Data.SqlClient.SqlException (0x80131904): Login failed for user 'Shizuku'. 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, SqlAuthenticationProviderManager sqlAuthProviderManager) 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, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource
1 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 (Code file was here) ClientConnectionId:953f6859-a48b-4f58-a0b8-bef0702911a3 Error Number:18456,State:1,Class:14
I feel as if that error means I'm closer to solving the issue currently, but I'm not entirely sure. Is there anything I haven't tried or any error I've made that I just simply missed?