Issue: Login failed for user MLXSQLService.
Explanation: We have 2 users to access our database, msadmin and MLXSQLService. The msadmin user is the admin of the SQL Database and has all the permissions to edit, delete etc. on the database. The other user, MLXSQLService has only two permissions: Database read, and database write. The password for msadmin is defined as the value of administratorLoginPassword in our parameters file and the password for MLXSQLService is defined by the value of databasePassword in our parameters file.
In our database connection string, we are using the MLXSQLService user to access the database. The value of database connection string that goes into KeyVault/App Settings, is created at the time of the website deployment. It uses MLXSQLService as the database user. For the password of that user, we are using the value of the databasePassword that is contained in the parameters.json file, or if it not there in parameters.json file, then it is specified in the powershell (when asked). Afterwards, when the database is deployed, we need to supply a value for the password of the user MLXSQLService. This needs to be the same value as supplied in the parameters.json file as databasePassword.
In our earlier deployments, we have been using a default value for the password of MLXSQLService by clicking on the “Load values” button in Visual Studio. For those deployments, the same value needs to be passed in the parameters.json file also as the databasePassword parameter, otherwise we will get this error.
This error is shown when the value passed in parameters.json file is not same as the value supplied while deploying database.
Resolution: The issue “Login failed for user MLXSQLService” is because of password mismatch in mlxcontainer connection string and in database.
- Please check the mlxcontainer connection string at KeyVault > Secrets > MlxContainer > Current Version > “Show secret value”
- To modify the connection string, please copy the MlxContainer’s Current Version secret value to a notepad and modify the Password.MlxContainer Connection String sample -> metadata=res://*/Mlx.csdl|res://*/Mlx.ssdl|res://*/Mlx.msl;provider=System.Data.SqlClient;provider connection string=”Data Source=tcp:sqlservername.database.windows.net,1433;Initial Catalog=dbname;Persist Security Info=False;User Id=MLXSQLService@sqlservername;Password=<Password>;MultipleActiveResultSets=True;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;App=EntityFramework”
- Now at KeyVault > Secrets > MlxContainer > + New VersionUpload options = Manual Name = MlxContainer Value = <Paste here the modified connection string>
- Once created, copy value fromKeyVault > Secrets > MlxContainer > Current Version > Secret Identifier and paste it to App Service > Application Settings > MlxContainer, and then Save.
- Please Restart the App Service.
Issue: Unable to connect to Azure SQL database from local machine
- An Azure SQL Database server listens on port 1433. To connect to a SQL Database server from behind a corporate firewall, the firewall must have this port open.
- SQL Database communicates over port 1433. If you’re trying to connect from within a corporate network, outbound traffic over port 1433 might not be allowed by your network’s firewall. If so, you can’t connect to your Azure SQL Database server unless your IT department opens port 1433.