Dev Blog: Exporting a Database with SqlPackage.exe and Creating Users in SSMS

A streamlined process to export a SQL database using SqlPackage.exe and tackle common issues such as orphaned users and certificate errors. I’ll also include a brief guide on how to create a user via SQL Server Management Studio (SSMS).

Exporting the Database with SqlPackage.exe

1. Prepare the Database:
   Ensure that your database has all necessary data and that any users are correctly linked to their corresponding logins. If you have orphaned users (users without matching server logins), either remove them or create the corresponding logins.

2. Use SqlPackage.exe: (Can be downloaded from microsoft or installed with dotnet.exe
   Use the following command from a Command Prompt or PowerShell session to export your database to a BACPAC file. 

   batch
   SqlPackage.exe /Action:Export /SourceConnectionString:"Server=YOUR_SERVER;Database=YOUR_DATABASE;Trusted_Connection=True;Encrypt=True;TrustServerCertificate=True;" /TargetFile:"E:\BackupSites\YOUR_DATABASE\YOUR_DATABASE.bacpac"
   

   Replace YOUR_SERVER, YOUR_DATABASE, and the target path with your actual server name, database name, and desired file location.This example assumes Windows Authentication and includes parameters to trust the server certificat

Troubleshooting Common Issues

• Orphaned Users:
  If you encounter errors about orphaned users during export, verify that each user in the database is associated with a corresponding login.

  • Solution: Either remove the orphaned users or create matching logins and link them using:

    sql
    -- Create login if not exists
    IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = 'UserName')
    BEGIN
        CREATE LOGIN [UserName] WITH PASSWORD = 'YourStrongPassword!'
    END
    
    USE YourDatabase;
    ALTER USER [UserName] WITH LOGIN = [UserName];
    

• Certificate Issues:
  If you see errors like "The certificate chain was issued by an authority that is not trusted," add TrustServerCertificate=True (or set Encrypt=False if encryption is not needed) in your connection string.

Creating a User via SQL Server Management Studio

1. Create a Login:

   • Open SSMS and connect to your server.
   • In Object Explorer, expand the Security folder.
   • Right-click on Logins and select New Login...
   • Enter a login name and select the appropriate authentication method (e.g., Windows Authentication or SQL Server Authentication).
   • Click OK to create the login.

2. Map the Login to a Database User:

   • Expand the newly created login under the Logins folder.
   • Right-click on the login and choose Properties.
   • In the User Mapping page, select the database you want the user to access.
   • Check the Map checkbox and assign the necessary database role memberships.
   • Click OK to complete the process.

Using these steps, you can ensure a smooth export process with SqlPackage.exe and properly manage user logins and mappings within SSMS.

Finally, to import the BACPAC file into Azure using SSMS, connect to your Azure SQL Database. In Object Explorer, right-click the Databases folder and choose Import Data-tier Application. The Import wizard will prompt you to select a local BACPAC file; browse to the file you created earlier. Specify a target database name and configure any additional settings such as service tier and performance level. Follow through the remaining steps of the wizard to complete the import process. Once finished, your Azure SQL Database will contain the imported schema and data from your original database.

This approach helps you manage potential issues during export—such as orphaned users and certificate errors—and provides a clear workflow for both exporting with SqlPackage.exe and importing into Azure via SSMS. Happy coding and troubleshooting!