Skip to main content

Create a read-only SQL login for Aiwyn

Christa Mason
Updated

Create a dedicated SQL Server login that Aiwyn can use to read your practice management database. Do not reuse a person's login. Keep access read-only unless your firm uses Practice CS and requires the specific writeback permissions described below.

Before you start

You need:

  • SQL Server Management Studio and administrative access to the SQL Server.
  • The database name supplied to Aiwyn.
  • A unique login name and a strong password that can be shared through Aiwyn's approved secure method.
  • SQL Server authentication enabled. Local Windows accounts cannot be used for this connection.

Create the login

  1. Connect to the SQL Server in SQL Server Management Studio.
  2. In Object Explorer, expand Security → Logins.
  3. Right-click Logins and select New Login.
  4. Enter a unique, dedicated login name for the Aiwyn connection. Aiwyn does not require a specific naming convention.
  5. Select SQL Server authentication and enter a strong password. Clear Enforce password expiration so the password does not expire and unexpectedly interrupt the database connection. Coordinate any password changes with your Aiwyn implementation contact.
  6. Set the default database to the practice management database Aiwyn will read.
  7. Do not assign broad server roles. The login needs database read access, not administrative access. For Practice CS writeback, grant only the specific additional permissions confirmed by your Aiwyn implementation contact.

Map the login to the database

  1. In the login properties, select User Mapping.
  2. Select the practice management database Aiwyn will read.
  3. In database role membership, select db_datareader.
  4. Select OK to create the login and database user.

db_datareaderis a fixed database role that can read all user tables and views in that database. It does not grant insert, update, delete, schema-change, or administrative permissions.

If Aiwyn must read more than one database, confirm the required database list with your implementation contact before repeating the user mapping. Do not grant access to unrelated databases.

PracticeCS writeback

Firms using PracticeCS need additional write permissions on the specific table or view used for writeback. Before granting these permissions, confirm the required table or view and permissions with your Aiwyn implementation contact. Do not grant broad database write access; keep all other access read-only.

Verify the login

  1. Connect to SQL Server using the new SQL login.
  2. Open the target database.
  3. Run a simple SELECT against a known user table or view.
  4. Confirm that reading succeeds and that the login cannot modify data. For Practice CS writeback, verify that write access is limited to the specific table or view and permissions confirmed by your Aiwyn implementation contact.

Send the connection details securely

Aiwyn needs the host name, SQL Server port, database name, login name, and password. The default SQL Server port is 1433, but confirm the port used by your instance. Send credentials only through the secure method provided by your Aiwyn implementation contact.

The login is one part of the connection. Your network team must also configure IP allowlisting with either TLS encryption or the approved SSH tunnel so Aiwyn's ETL service can reach the SQL Server database.

Troubleshooting

  • Login failed: Confirm SQL Server authentication is enabled, the password is correct, and the login is enabled.
  • Cannot open database: Confirm the login is mapped to the target database and its default database is available.
  • SELECT permission denied: Confirm the database user belongs to db_datareader in the target database.
  • Connection times out: Check the SQL Server port, firewall rules, TLS or SSH configuration, and network route.

Related articles