Working with Windows Azure ist fun all day.
Today i created a new Virtual Machine with SQL Server 2012 SP1 on Windows Server 2008 R2 to test something out.
After the Virtual Machine was available
- i went to the Endpoints tab and configured a new Endpoint for SQL Server with TCP/IP Protocol on port 1433 both public and private
- went per Remote Desktop to my new VM checked the SQL Server Configuration Manager -> SQL Server Network Configuration -> Protocols for MSSQLSERVER -> TCP/IP -> Properties -> IP Addresses -> IP All that the TCP Port is set to 1433
- put firewall rules to let my clients connect on port 1433
and tried to connect from my local SQL Server Management Studio to my On Premise SQL Server running in the Windows Azure Virtual Machine.
Looking into the SQL Server Logfile, i found
Login failed for user ‘myOwnUsername’. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: <local machine>]
The solution is to go to the SSMS in the VM -> right click on the Instance -> Properties -> Security -> Server authentication: choose “SQL Server and Windows Authentication mode”.
So if you create a new VM in Azure from the “Quick Create Wizard”, remember that by default the Authentication mode ist not mixed, but only Windows.