Question: How to connect 3rd party applications to the ACT7 instance for Act! v21 or above
Product Details:
Product Family: Act! Pro, Premium
Version: v21 and later
Answer:
Due to changes in the way Act! secures the SQL instance (Which is a named instance called ‘ACT7’), it is no longer possible to obtain the SA password. In some cases this will affect how some 3rd party applications and integrations need to connect to the ACT7 instance.
By default the ACT7 instance is setup using Mixed mode authentication which allows both authorised Windows users and specifically created SQL users to login to the instance. To gain access to the ACT7 instance using your Windows user, follow the guidance below depending on your situation.
Note: The following information is provided as a convenience. Swiftpage does not provide support for Microsoft Products. For more information on this topic, go to Microsoft.com
There are two scenarios when attempting to authenticate with your Windows user when your ACT7 instance was installed by the Act! desktop software.
If you are manually installing the ACT7 instance, you will only need to make sure that you follow the steps provided in: How to manually create the Microsoft SQL Server (ACT7) instance
that requires you add your current Windows user as a SQL Server administrator. If this has been done correctly, you can connect to the ACT7 instance using Windows Authentication with any Windows user you add at this point in the installation.
Please refer to the section labelled 'Connecting to the ACT7 instance with Windows authentication' to continue.
If the ACT7 instance is already installed and the Windows user you are attempting to authenticate with fails, then it will be necessary to add yourself to the SQL instance as a SQL server administrator to be able to login.
Please find attached to this support article the script 'addselftosqlsysadmin_level 14.cmd' that will allow you to add the Windows user you are logged in as to the sysadmin role of your ACT7 instance.
Please Note:
This script will give the Windows user in question full access to the ACT7 instance and will be able to perform any task up to and including modifying the settings of the instance, modify tables and the data inside them, and deleting any attached databases. Make sure this is considered before adding a Windows user to this security role. More information on SQL Security roles can be found here: Server Level Roles
During the execution of this script your ACT7 instance will be stopped and restarted at least twice over a short period of time which will require any Act! users to be logged out of their database while it is ran to prevent any unintended data loss or unexpected loss of service.
Once this script is finished, you should be able to authenticate using your current windows credentials. Please refer to the section ‘Connecting to the ACT7 instance with Windows authentication’for guidance.
Information on connecting to a SQL instance in SQL Server management studio (SSMS) with Windows Authentication can be found at the following Microsoft article:
Connect to and query a SQL Server instance by using SQL Server Management Studio
and an overview of SQL Server Authentication can be found here:
Authentication in SQL Server
See below for an example of connecting to the ACT8 instance using SSMS.
Once you are connected to your ACT7 instance it is possible to create your own SQL server logins if required for your own automations or integrations. Please refer to the Microsoft documentation on creating new logins for your SQL instance.
Using SSMS: Authentication and access - Create a Login:
Using T-SQL: CREATE LOGIN (Transact-SQL):
Please Note: If logging in via Windows Authentication is not working, please refer to the section 'Allowing your Windows user to login to the ACT7 instance'.
Product Details:
Product Family: Act! Pro, Premium
Version: v21 and later
Answer:
Due to changes in the way Act! secures the SQL instance (Which is a named instance called ‘ACT7’), it is no longer possible to obtain the SA password. In some cases this will affect how some 3rd party applications and integrations need to connect to the ACT7 instance.
By default the ACT7 instance is setup using Mixed mode authentication which allows both authorised Windows users and specifically created SQL users to login to the instance. To gain access to the ACT7 instance using your Windows user, follow the guidance below depending on your situation.
Note: The following information is provided as a convenience. Swiftpage does not provide support for Microsoft Products. For more information on this topic, go to Microsoft.com
The ACT7 instance was setup automatically when I installed the Act! desktop software
There are two scenarios when attempting to authenticate with your Windows user when your ACT7 instance was installed by the Act! desktop software.
- You are logged in as the Windows user that installed Act!
You should be able to login to your ACT7 instance using Windows authentication without any further action. Please refer to the section labelled 'Connecting to the ACT7 instance with Windows authentication' to continue. - You are not using the Windows user who installed Act
In this case you will need to add yourself to the SQL sysadmin role for your ACT7 instance. Please refer to the section labelled 'Allowing your Windows user to login to the ACT7 instance'.
You are manually installing the ACT7 instance
If you are manually installing the ACT7 instance, you will only need to make sure that you follow the steps provided in: How to manually create the Microsoft SQL Server (ACT7) instance
that requires you add your current Windows user as a SQL Server administrator. If this has been done correctly, you can connect to the ACT7 instance using Windows Authentication with any Windows user you add at this point in the installation.
Please refer to the section labelled 'Connecting to the ACT7 instance with Windows authentication' to continue.
Allowing your Windows user to login to the ACT7 instance
If the ACT7 instance is already installed and the Windows user you are attempting to authenticate with fails, then it will be necessary to add yourself to the SQL instance as a SQL server administrator to be able to login.
Please find attached to this support article the script 'addselftosqlsysadmin_level 14.cmd' that will allow you to add the Windows user you are logged in as to the sysadmin role of your ACT7 instance.
Please Note:
This script will give the Windows user in question full access to the ACT7 instance and will be able to perform any task up to and including modifying the settings of the instance, modify tables and the data inside them, and deleting any attached databases. Make sure this is considered before adding a Windows user to this security role. More information on SQL Security roles can be found here: Server Level Roles
During the execution of this script your ACT7 instance will be stopped and restarted at least twice over a short period of time which will require any Act! users to be logged out of their database while it is ran to prevent any unintended data loss or unexpected loss of service.
Once this script is finished, you should be able to authenticate using your current windows credentials. Please refer to the section ‘Connecting to the ACT7 instance with Windows authentication’for guidance.
How to use the 'addselftosqlsysadmin_level 14.cmd' script
- Save the attached file to a safe location on the computer or server that hosts your ACT7 instance
- Right-Click the file and select the option 'Run as Administrator' from the context menu
- When prompted, enter the instance name of ACT7 and press ENTER
- The script will briefly prompt you that it is adding your current Windows user to the sysadmin role of your ACT7 instance and then close when complete
- Once complete you should be free to login using Windows authentication – refer to the section 'Connecting to the ACT7 instance with Windows authentication' for guidance
Connecting to the ACT7 instance with Windows authentication
Information on connecting to a SQL instance in SQL Server management studio (SSMS) with Windows Authentication can be found at the following Microsoft article:
Connect to and query a SQL Server instance by using SQL Server Management Studio
and an overview of SQL Server Authentication can be found here:
Authentication in SQL Server
See below for an example of connecting to the ACT8 instance using SSMS.
Once you are connected to your ACT7 instance it is possible to create your own SQL server logins if required for your own automations or integrations. Please refer to the Microsoft documentation on creating new logins for your SQL instance.
Using SSMS: Authentication and access - Create a Login:
Using T-SQL: CREATE LOGIN (Transact-SQL):
Please Note: If logging in via Windows Authentication is not working, please refer to the section 'Allowing your Windows user to login to the ACT7 instance'.