Connecting to SQL Server remotely that is using windows authentication

Question

I know there are several threads about this topic, i checked them but situation is different.
so i have a remote SQL server, it has its (external) IP address, and I can RDP to it, open SSM and it uses the windows authentication to log in to it.

Now my problem is, I want to connect from a different device remotely, I open SSM in another computer i set IP address in Server Name and in Authentication window, i select "SQL Server Authentication" as I cannot just use "Windows Authentication" as it will use my local PC authentication. But its obvious it will not work, and I have errors such as "Invalid password" or if I use "Windows Authentication" I will have the "The target principal name is incorrect" error.

So how to do this?
How do i connect from my local machine to my remote SQL Server that uses Windows Authentication?

Answer 1

Hi @cebuhax0r

You have two options... if your two devices are located in the same Windows Domain you should be able to use your Windows Account. If not, and you want/need to login to your SQL Server, you have to create a login first.

CREATE LOGIN <login_name> WITH PASSWORD = '<enterStrongPasswordHere>';  
GO  

https://learn.microsoft.com/en-us/sql/t-sql/statements/create-login-transact-sql?view=sql-server-ver16#examples

and grant permissions

ALTER SERVER ROLE sysadmin ADD MEMBER <loginname> ;    

https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-server-role-transact-sql?view=sql-server-ver16#examples

Answer 2

Hi @cebuhax0r ,

The target principal name is incorrect" error.

https://learn.microsoft.com/en-us/troubleshoot/sql/connect/cannot-generate-sspi-context-error?source=recommendations

To connect to SQL Server remotely, it is better to in a same Windows domain, allow access to users first, you need to create new login, make sure the user is a Windows authentication user, and then enable port to allow access to the computer through a specific port

Please check out this article:
https://social.technet.microsoft.com/wiki/contents/articles/1533.how-to-enable-remote-connections-on-sql-server.aspx#:~:text=%20To%20enable%20port%201433%20on%20Windows%20Vista,SQL%20Server%20Port.%206%20Click%20OK.%20See%20More.

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.