SmartConnect 2010 uses Active Directory-based security instead of SQL security. This has caused some confusion among partners who are used to the ‘sa’ user being able to access everything. We have received calls from partners who are frustrated because the ‘sa’ user cannot get into SmartConnect Setup or SmartConnect security.
During the initial installation of SmartConnect, one of the last steps after launching Great Plains should be to go into Tools – SmartConnect – Security. If there are no users configured, a window will pop open informing the user that there are no users setup, and asks if you would like to setup the current user as an administrator. When the user clicks OK, it adds the users Windows login as an administrator and not their SQL or Great Plains login.
This user will then be able to access SmartConnect security setup, and should add the additional domain users to SmartConnect security. The user lookup is actually looking for Active Directory domain users and not SQL users.
If no users are unable to access SmartConnect setup or security, you need to find out who the SmartConnect administrator is by: logging into SQL Server Management Studio and running a select query on Users table in the SmartConnect database. Any domain user listed there that has the “IsAdmin” checkbox marked is an Administrator in SmartConnect. If you log out of Windows, and log back in as that user, you then will be able to access SmartConnect security and add additional users.
There are a number of reasons why SmartConnect is configured to work with AD. The primary reason being that many of the users that will execute a map (especially via Excel or other remote applications) may not have any login access to GP. In order to control access for these users the security needs to be based on something other than traditional GP security.