CODEWITHSUNDEEP
sqlsql-serverazure
Abhijeet
Abhijeet

Reputation: 13856

Connect to Sql Server 2014 database into Azure VM

I just created a new SQL Server 2014 VM from Windows Azure gallery. I did remote login into VM , on trying to login into DB with Windows Authentication mode, I get below error:

Login failed for user 'dbserver\dba1'. (.Net SqlClient Data Provider)

Are there any specific steps, which I have to follow for new SQL Server DB login to work properly?

P.S. I have opened port 1433 into windows firewall, it did not help, althoug that should not be required for logging in directly from VM.

Upvotes: 2

Views: 1564

Answers (3)

Gulf State Software
Gulf State Software

Reputation: 51

I was getting connection inside the VM using MSSQL Server, but not from outside. After reading some comments, I found that I needed to add a specific port for outgoing traffic and added that port at Azure firewall:

Here are the steps to check is it working for you or not:

  1. Start Sql Server configuration Manager

  2. Click on your Server name

  3. Click SQL Server network config

  4. Click on protocols

  5. Click on TCP/IP

  6. At the end of tcp/IP range set your port, I set 1433.

  7. Please restart SQL Database.

enter image description here
Now open port at Azure management portal. And conntect through SQL tools or other services.

enter image description here

And also remember to open windows firewall.

Upvotes: 1

Evgeny Krivosheev
Evgeny Krivosheev

Reputation: 86

I guess Azure VM is not joined to domain. This means that only SQL authentication will work against this SQL Server instance. You might need to do the following:

  • Enable Mixed authentication on the SQL Server
  • Add a user mapped to SA role (or enable SA and set its password, but having separate user is better)
  • Open port 1433 (you did this already)
  • Make sure SQL is listening on this port (should be already)
  • In case you connecting outside of the same Azure Cloud Service - make sure you have endpoint created for the VM. Highly recommended to use port different from 1433 as the endpoint public port.

Don't forget to specify port if it is not standard, SQL connection string expects comma for it (i.e. myvmpublicname.cloudapp.net,12345)

You can check this article for more details

Upvotes: 3

Adam
Adam

Reputation: 16199

It most likely installed another windows user as the admin for the SQL Database. If you know what that user is, login as them.

Otherwise, did you manage to set up the SQL in mixed mode and have an sa password. If so, login with that then add the Windows Account.

If not, try Forgot SQL Server Password to reset the sa password and get access that way.

Upvotes: 0

Related Questions