I'm in the process of moving an EpiServer site from a Windows 2008 environment into a Windows 2016 / MSSQL Server 2017 environment. I'm struggling to get the application to connect to the MSSQL cluster. Using the settings found in the ConnectionStrings section of web.xml, I ran a Data Link Properties connection test with the hostname, username and password - along with the dataabase name. The connection works.
When the site is accessed, all that occurs is the following in the Event Viewer:
Exception information: Exception type: SqlException Exception message: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) 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 System.Web.SessionState.SqlSessionStateStore.SqlStateConnection..ctor(SqlPartitionInfo sqlPartitionInfo, TimeSpan retryInterval)
Looking at the named pipes on the SQL Server, apart from the hostname (of which the connection string has been updated for), everything looks fine. I can't see anything obvious.
Has anybody else encountered an issue like this?
named pipes are used only when sql server is runing in the same machine. if sql and episerver resides in 2 different machines you should verify that tcp protocol is enabled in sql server side (think by default it might be disabled). other than that - don't think it's really related to episerver, but could be classified and general sqlserver connectivity issue question..
Yes, that's what I thought. I checked the existing environment and TCP/IP is enabled - though in the SQL Configuration Manager, it is not active for specific IPs. Looking at the existing environment this is exactly the same. So how the existing environment is able to connect, I don't know. I should mention these are clustered SQL servers and we're connecting directly to the cluster.
Can you post your connectionStrings here? And did you allow the connection to SQL Server in the firewall?
please omit password :troll:
Many thanks. We discovered that another string was present within the many configuration files (relating to the web session) that was referencing the old environment. We updated the host, username and password and was able to connect - though we now see:
Exception information: Exception type: SqlException Exception message: Invalid object name 'tempdb.dbo.ASPStateTempApplications'.
and wondering if we need to drop the ASPState database that we copied from the old environment or not.
if you created new database (and note moved the old one) then you will need to re-register asp.net session storage schema. lookup syntax for "aspnet_regsql.exe" tool (option -ssadd).