virtual machines - Install SQL Server 2017 on Windows 10 Azure VM

I have one Azure Windows 10 VM set up as a workstation which has among other things:

• Visual Studio 2017


• Python 2.7


• NodeJS


• SQL Server 2017

When a coworker created a new Windows 10 VM and went to install SQL Server, he got an error. I then created a new Windows 10 VM and attempted to install SQL Server and got the same error. Thinking it had something to do with the VM configuration I chose the original configuration which worked, tried again, and it again failed.

Here's the Windows 10 machines we've used
Standard D2 v2 (2 vcpus, 7 GB memory): Success
Standard DS11 v2 (2 vcpus, 14 GB memory): Fail
Standard DS11 v2 (2 vcpus, 14 GB memory): Fail
Standard D2 v2 (2 vcpus, 7 GB memory): Fail

This is from the install Detail file:
...

(01) 2018-01-05 18:10:04 Slp: Current SqlServer Connection closed...
(01) 2018-01-05 18:10:04 Slp: Configuration action failed for feature SQL_Engine_Core_Inst during timing ConfigRC and scenario ConfigRC.
(01) 2018-01-05 18:10:04 Slp: 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)
(01) 2018-01-05 18:10:04 Slp: The configuration failure category of current exception is ConfigurationFailure
(01) 2018-01-05 18:10:05 Slp: Configuration action failed for feature SQL_Engine_Core_Inst during timing ConfigRC and scenario ConfigRC.
(01) 2018-01-05 18:10:05 Slp: Microsoft.SqlServer.Configuration.Sco.ScoException: 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) ---> System.Data.SqlClient.SqlException: 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) ---> System.ComponentModel.Win32Exception: The system cannot find the file specified
(01) 2018-01-05 18:10:05 Slp: --- End of inner exception stack trace ---
(01) 2018-01-05 18:10:05 Slp: 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)
(01) 2018-01-05 18:10:05 Slp: at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
(01) 2018-01-05 18:10:05 Slp: at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
(01) 2018-01-05 18:10:05 Slp: at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
(01) 2018-01-05 18:10:05 Slp: at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions)
(01) 2018-01-05 18:10:05 Slp: at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource1 retry)
(01) 2018-01-05 18:10:05 Slp: at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource1 retry)
(01) 2018-01-05 18:10:05 Slp: at System.Data.SqlClient.SqlConnection.Open()
(01) 2018-01-05 18:10:05 Slp: at Microsoft.SqlServer.Configuration.Sco.SqlScriptExecution.GetConnection()
(01) 2018-01-05 18:10:05 Slp: at Microsoft.SqlServer.Configuration.Sco.SqlScriptExecution.Connect()
(01) 2018-01-05 18:10:05 Slp: --- End of inner exception stack trace ---
(01) 2018-01-05 18:10:05 Slp: at Microsoft.SqlServer.Configuration.Sco.SqlScriptExecution.Connect()
(01) 2018-01-05 18:10:05 Slp: at Microsoft.SqlServer.Configuration.SqlEngine.SqlDatabaseServiceConfig.Install(SqlServiceInstallScriptParameters scriptParameters, Boolean fTemplateDB)
(01) 2018-01-05 18:10:05 Slp: at Microsoft.SqlServer.Configuration.SqlEngine.SqlEngineDBStartConfig.ConfigSQLServerSystemDatabases(EffectiveProperties properties, Boolean isConfiguringTemplateDBs, Boolean useInstallInputs)
(01) 2018-01-05 18:10:05 Slp: at Microsoft.SqlServer.Configuration.SqlEngine.SqlEngineDBStartConfig.DoCommonDBStartConfig(ConfigActionTiming timing)
(01) 2018-01-05 18:10:05 Slp: at Microsoft.SqlServer.Configuration.SqlEngine.SqlEngineDBStartConfig.Install(ConfigActionTiming timing, Dictionary2 actionData, PublicConfigurationBase spcb)
(01) 2018-01-05 18:10:05 Slp: at Microsoft.SqlServer.Configuration.SqlConfigBase.PrivateConfigurationBase.Execute(ConfigActionScenario scenario, ConfigActionTiming timing, ConfigBaseAction action, Dictionary2 actionData, PublicConfigurationBase spcbCurrent)
(01) 2018-01-05 18:10:05 Slp: at Microsoft.SqlServer.Configuration.SqlConfigBase.SqlFeatureConfigBase.Execute(ConfigActionScenario scenario, ConfigActionTiming timing, ConfigBaseAction action, Dictionary2 actionData, PublicConfigurationBase spcbCurrent)
(01) 2018-01-05 18:10:05 Slp: at Microsoft.SqlServer.Configuration.SqlConfigBase.SlpConfigAction.ExecuteAction(String actionId)
(01) 2018-01-05 18:10:05 Slp: at Microsoft.SqlServer.Configuration.SqlConfigBase.SlpConfigAction.Execute(String actionId, TextWriter errorStream)
(01) 2018-01-05 18:10:05 Slp: The following is an exception stack listing the exceptions in outermost to innermost order
(01) 2018-01-05 18:10:05 Slp: Inner exceptions are being indented
(01) 2018-01-05 18:10:05 Slp:
(01) 2018-01-05 18:10:05 Slp: Exception type: Microsoft.SqlServer.Configuration.Sco.ScoException
(01) 2018-01-05 18:10:05 Slp: Message:
(01) 2018-01-05 18:10:05 Slp: 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)
(01) 2018-01-05 18:10:05 Slp: HResult : 0x84bb0001
(01) 2018-01-05 18:10:05 Slp: FacilityCode : 1211 (4bb)
(01) 2018-01-05 18:10:05 Slp: ErrorCode : 1 (0001)
(01) 2018-01-05 18:10:05 Slp: Data:
(01) 2018-01-05 18:10:05 Slp: DisableRetry = true
(01) 2018-01-05 18:10:05 Slp: SQL.Setup.FailureCategory = ConfigurationFailure
(01) 2018-01-05 18:10:05 Slp: WatsonConfigActionData = INSTALL@CONFIGRC@SQL_ENGINE_CORE_INST
(01) 2018-01-05 18:10:05 Slp: WatsonExceptionFeatureIdsActionData = System.String[]
(01) 2018-01-05 18:10:05 Slp: Stack:
(01) 2018-01-05 18:10:05 Slp: at Microsoft.SqlServer.Configuration.Sco.SqlScriptExecution.Connect()
(01) 2018-01-05 18:10:05 Slp: at Microsoft.SqlServer.Configuration.SqlEngine.SqlDatabaseServiceConfig.Install(SqlServiceInstallScriptParameters scriptParameters, Boolean fTemplateDB)
(01) 2018-01-05 18:10:05 Slp: at Microsoft.SqlServer.Configuration.SqlEngine.SqlEngineDBStartConfig.ConfigSQLServerSystemDatabases(EffectiveProperties properties, Boolean isConfiguringTemplateDBs, Boolean useInstallInputs)
(01) 2018-01-05 18:10:05 Slp: at Microsoft.SqlServer.Configuration.SqlEngine.SqlEngineDBStartConfig.DoCommonDBStartConfig(ConfigActionTiming timing)
(01) 2018-01-05 18:10:05 Slp: at Microsoft.SqlServer.Configuration.SqlEngine.SqlEngineDBStartConfig.Install(ConfigActionTiming timing, Dictionary2 actionData, PublicConfigurationBase spcb)
(01) 2018-01-05 18:10:05 Slp: at Microsoft.SqlServer.Configuration.SqlConfigBase.PrivateConfigurationBase.Execute(ConfigActionScenario scenario, ConfigActionTiming timing, ConfigBaseAction action, Dictionary2 actionData, PublicConfigurationBase spcbCurrent)
(01) 2018-01-05 18:10:05 Slp: at Microsoft.SqlServer.Configuration.SqlConfigBase.SqlFeatureConfigBase.Execute(ConfigActionScenario scenario, ConfigActionTiming timing, ConfigBaseAction action, Dictionary2 actionData, PublicConfigurationBase spcbCurrent)
(01) 2018-01-05 18:10:05 Slp: at Microsoft.SqlServer.Configuration.SqlConfigBase.SlpConfigAction.ExecuteAction(String actionId)
(01) 2018-01-05 18:10:05 Slp: at Microsoft.SqlServer.Configuration.SqlConfigBase.SlpConfigAction.Execute(String actionId, TextWriter errorStream)
(01) 2018-01-05 18:10:05 Slp: Inner exception type: System.Data.SqlClient.SqlException
(01) 2018-01-05 18:10:05 Slp: Message:
(01) 2018-01-05 18:10:05 Slp: 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)
(01) 2018-01-05 18:10:05 Slp: HResult : 0x80131904
(01) 2018-01-05 18:10:05 Slp: Data:
(01) 2018-01-05 18:10:05 Slp: HelpLink.ProdName = Microsoft SQL Server
(01) 2018-01-05 18:10:05 Slp: HelpLink.EvtSrc = MSSQLServer
(01) 2018-01-05 18:10:05 Slp: HelpLink.EvtID = 2
(01) 2018-01-05 18:10:05 Slp: HelpLink.BaseHelpUrl = http://go.microsoft.com/fwlink
(01) 2018-01-05 18:10:05 Slp: HelpLink.LinkId = 20476
(01) 2018-01-05 18:10:05 Slp: Stack:
(01) 2018-01-05 18:10:05 Slp: 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)
(01) 2018-01-05 18:10:05 Slp: at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
(01) 2018-01-05 18:10:05 Slp: at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
(01) 2018-01-05 18:10:05 Slp: at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
(01) 2018-01-05 18:10:05 Slp: at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions)
(01) 2018-01-05 18:10:05 Slp: at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource1 retry)
(01) 2018-01-05 18:10:05 Slp: at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource1 retry)
(01) 2018-01-05 18:10:05 Slp: at System.Data.SqlClient.SqlConnection.Open()
(01) 2018-01-05 18:10:05 Slp: at Microsoft.SqlServer.Configuration.Sco.SqlScriptExecution.GetConnection()
(01) 2018-01-05 18:10:05 Slp: at Microsoft.SqlServer.Configuration.Sco.SqlScriptExecution.Connect()
(01) 2018-01-05 18:10:05 Slp: Inner exception type: System.ComponentModel.Win32Exception
(01) 2018-01-05 18:10:05 Slp: Message:
(01) 2018-01-05 18:10:05 Slp: The system cannot find the file specified
(01) 2018-01-05 18:10:05 Slp: HResult : 0x80004005
(01) 2018-01-05 18:10:05 Slp: Error : 2
(01) 2018-01-05 18:10:05 Slp: Watson Bucket 1
Original Parameter Values

...

SQL Server installed to some degree, but it won't start. When telling it to start, this is in the event log:

FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'd:\dbs\sh\sprel\0822_164025\cmd\33\obj\x64retail\sql\mkmastr\databases\mkmastr.proj\modellog.ldf'. Diagnose and correct the operating system error, and retry the operation.

For the above error, while I do have a d: drive, it's the Azure VM "Temporary Storage" so nothing should be looking for files there.

Answer

Found the answer at https://social.technet.microsoft.com/wiki/contents/articles/31786.sql-server-not-starting-after-fresh-installation.aspx

Open a command, and start SQL Server:

net start MSSQL$SQLEXPRESS /f /t3608

Then open a SQL command prompt:

sqlcmd -S .\SQLEXPRESS

Verify that SQL Server is pointing to files in the wrong place. You should see which db and log file paths are incorrect:

1> select name, physical_name, state_desc from sys.master_files order by database_id;
2> go

Grab the correct paths to those files, then build and run this query:

ALTER DATABASE model MODIFY FILE ( NAME = modeldev, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\model.mdf');

ALTER DATABASE model MODIFY FILE ( NAME = modellog, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\modellog.ldf');
ALTER DATABASE msdb MODIFY FILE ( NAME = MSDBData, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\MSDBData.mdf');
ALTER DATABASE msdb MODIFY FILE ( NAME = MSDBLog, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\MSDBLog.ldf');
ALTER DATABASE tempdb MODIFY FILE ( NAME = tempdev, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\temp.mdf');
ALTER DATABASE tempdb MODIFY FILE ( NAME = templog, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\temp.ldf');
go

Exit, stop, then start SQL Server:

1> exit
net stop MSSQL$SQLEXPRESS
net start MSSQL$SQLEXPRESS

Fire up SSMS, attempt to connect to the instance, and notice that you can't connect even with Windows auth. Now, stop the server, restart again in recovery mode, and give sa a password:

net stop MSSQL$SQLEXPRESS
net start MSSQL$SQLEXPRESS /f /t3608
sqlcmd -S .\SQLEXPRESS


ALTER LOGIN sa WITH PASSWORD = 'newpassword' UNLOCK
go
exit

Make sure the server is configured with SQL Server auth. Open RegEdit and ensure the value of Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQLServer\LoginMode is 2. If you can't find this key, do a search for LoginMode as it might be different for you.

Stop the recovery mode server, and start the regular server

net stop MSSQL$SQLEXPRESS
net start MSSQL$SQLEXPRESS

You should now be able to log in with sa and the password you set earlier