Symptom: When trying o provision a new website or database, you receive the following error message:
Error Number: 18456 Error Code: -2146232060 Message: Login failed for user 'yourusername'
Solution: The username or password you specified for your database is incorrect; you should be using the credentials your provided when you provisioned your existing SQL server, not what you would like as a virtual user to provision with the new database.
Since it is early this AM, I rushed through the portal thinking, oh since I am creating a new database, the portal will prompt me to create a new virtual account; this is not intended functionality. In the screenshot below you can see we select "Create a new SQL database"
In this scenario, I actually wish to use an existing database server I have already provisioned. When using an existing database, you will need to provide the credentials to the server itself so the new database can be provisioned. If you wish to add a separate user account that only has access to this database for a specific application, which is a recommended practice for security, you will need to use SQL Manager, Visual Studio, or another utility to connect to the database and provision a new virtual user account with privileges to this particular database.
So today I was digging through event viewer and saw some weird logs with the following error:
Cannot update active Match Making server because SQL Server does not respond.
The service failed to register an active Match Making because of a failure to connect to the SQL Server domainfrontend.local\rtc.
Exception: 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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
Inner Exception: ~
Cause: The server lost the connection to the backend database.
Make sure the machine has connectivity to the backend SQL Server.
After doing a quite Google search, nothing came up like usual, however I believe I have found a fix. When installing the latest cumulative update for your front end server, make sure that you don't forget to run the following command (I forgot to finish the install running this command on my second front end, which is when these errors started to popup).
Install-CsDatabase -Update -ConfiguredDatabases -SqlServerFqdn <EEBE.Fqdn> -UseDefaultSqlPaths
Hope this helps!
Here is how to enable remote SQL connections for Server 2008:
Open up SQL Server Configuration Manager
Click on SQL Server Network Configuration
--Enable Named Pipes
Protocols for SQLEXPRESS (OR YOUR INSTANCE)
-Double click on TCP/IP
Find the IP addresses with your IP and IP of 127.0.0.1
--TCP Dyanmic Ports (Remove the 0 and leave empty)
--TCP Port 1433
Open up Windows Firewall with Advanced Security
-Click on Inbound Rules
--Create the following rules
1433 - TCP - Check if you want Domain, Private, and Public Access
1434 - UDP - Check if you want Domain, Private, and Public Access
Transact-SQL debugger (Used for Visual Studio)
135 - TCP - Check if you want Domain, Private, and Public Access