Azure Virtual Machines – with SQL Server
28-Apr-1414 1 Comment
Setting up a VM for SQL Server Web Edition
- Created my first virtual machine with SQL Server Web edition on it.
- Added a windows user. Added the equivalent SQL Server windows user.
- Checked could login to SQL Server OK
- Used sysprep as a starting point
- Then took an image.
- Then created a new virtual machine from that image
- Logged in to new VM ok – but error on logging in to SQL Server.
- Then after a weekend the error had changed to “A connection was successfully established with the server, but then an error occurred during the login process (provider: Shared Memory Provider: 0 – No process is on the other end of the pipe)(Microsoft SQL Server, Error: 233)
- This was because SQL Server Agent was not running – started it
- In fact in SQL Server Configuration SQL Server Agent and Browser had to be changed to automatic startup
- Then back to original error
Error Login failed for user (Microsoft SQL Server Error 18456) on VM with SQL Server
- It seems that the user who created the SQL login on the first machine is not the same as the user who created the new machine after the sysprep
- So destroyed the machine and started again.
- New strategy – put SQL Server into Mixed Authentication Mode
- Create a SQL Server Login
- Give that login sysadmin
- Create the image.
- Create the VM from the image.
- Confirm that the Windows logins do not work for SQL Server
- Login as that SQL Server login admin created before, rather than the Windows Authentication only.
- Then script to drop and create the windows logins
- Issue resolved
Mixed Authentication Mode is required if logging into the SQL Server from another machine in any case.
So if creating a new VM from the image then start from step 7 above
Error: Login failed for remote SQL server
Solution
In the VM > Network and Sharing Center > View your active networks
The network had been set to private – reset to public and it now works again
How to setup a Virtual Machine for connection to SQL Server from another Machine
http://azure.microsoft.com/en-us/documentation/articles/virtual-machines-provision-sql-server/