Using Parallels for Mac / connecting to SQL Server 2017 in Windows 10 VM

Discussion in 'Parallels Desktop for Mac' started by TheNathan187, Dec 17, 2019.

  1. TheNathan187

    TheNathan187 Bit Poster

    Messages:
    7
    I've seen threads and postings about connecting to a database on a Windows VM. I don't necessarily have a problem regarding the configuration, setup. I have enabled TCP connections, browser agent is running and i opened the correct port on the firewall on the windows vm.

    I am using visual studio code (mac) to work on an angular web project and visual studio for mac to create the api. On the windows vm, there is a sql server 2017 instance with various database.

    My api can connect to the sql server database and I can query data. No problems there. The problem is with the database I try to connect to.
    If I create a database from scratch and the security logins, it works fine.

    The problem exists when I try to connect to a database that I restored from a database backup (bak file) from another machine. When my api tries to connect to a database that I restored from a .bak file...I always get a login error Login failed for user 'test_user'

    I have tried to remap the orphan login via: EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password' or EXEC sp_change_users_login 'Auto_Fix', 'user'....I still get the error

    I have also tried created a new login on that database server and map it accordingly......still get the error

    Has anyone else had this happen and what did you do to fix this? Thanks in advance
     
  2. TheNathan187

    TheNathan187 Bit Poster

    Messages:
    7
    finally figured out the issue.
    so on the vm that has sql server installed on it....i had about 3 different instances installed. a 2014, 2016, and a 2017. the database i was trying to connect to is a 2017 database...so it was restored on the 2017 instance. when i was trying to connect to the database through my web api, it kept looking for the database on the default instance (which was the 2012 instance). took a few to figure it out but it was annoying. i fought for about 45 minutes trying to figure out the right connection string to include ip address\instance,port. however, everything i tried did not work. so i took the punk a$$ route of uninstalling everything sql server and installing 2017 developer. luckily, my other instances didn't have important databases.

    lastly, here are some quick notes if you are going to use visual studio for mac and connect to a sql database on a parallels vm

    1. if you are restoring a database or creating one from scratch, do it on the default server instance. you probably can use a database on any instance but figuring out the connection string will suck
    2. make sure your instance has tcp/ip enabled, the browser and agent services are running and that your default instance is listening on port 1433. you can probably change the instance's port to your liking
    3. make sure that your sql login is mapped properly on the database server. if you restore from a .bak, your sql login will be orphaned. Use: EXEC sp_change_users_login 'Auto_Fix', 'user'
    4. sample connection string: 'Data Source=tcp:<ip address>,1433;Initial Catalog=<dbName>;User ID=<userID>;Password=<password>;'
    5. if your api or web project is using [Authorize] decorators...temporarily comment them out. I think there is a way to impersonate a windows user from the mac. i think you can create a kerberos certificate on the mac and it can be used to simulate a windows user. sorry, too lazy to figure that out if it worked
    i hope this helps someone
     

Share This Page