Knowledge Base
PROGRAM: SQL Configuration


Table of Contents
DataPlus SQL Security
DataPlus SQL Security
    Recomended DataPlus SQL Security
    DataPlus
  1. db_owner of DataPlus database
  2. db_owner of application database
    DataPlus Client
  1. db_owner of DataPlus database
  2. db_datareader of application database
SQL Security
SQL Server Security
    Server
  1. Option 1: Typical security for DataPlus is Windows authentication
  2. Option 2: sa User
  3. Option 3: custom User with database rights
Authentication in SQL Server
SQL Server supports two authentication modes, Windows authentication mode and mixed mode.
  • Windows authentication is the default, and is often referred to as integrated security because this SQL Server security model is tightly integrated with Windows. Specific Windows user and group accounts are trusted to log in to SQL Server. Windows users who have already been authenticated do not have to present additional credentials.
  • Mixed mode supports authentication both by Windows and by SQL Server. User name and password pairs are maintained within SQL Server.
Security Note:
    Microsoft recommends using Windows authentication wherever possible. Windows authentication uses a series of encrypted messages to authenticate users in SQL Server. When SQL Server logins are used, SQL Server login names and passwords are passed across the network, which makes them less secure.
Note:
    Logins are distinct from database users. You must map logins or Windows groups to database users or roles in a separate operation. You then grant permissions to users or roles to access database objects.
SQL Server Security
    Login Types
    Server
  1. Option 1: Typical security for DataPlus client is Windows authentication
  2. Option 2: Windows Group, users are members of the group
  3. Option 3: Windows user login
SQL Server supports three types of logins:
SQL Server supports two authentication modes, Windows authentication mode and mixed mode.
  • A local Windows user account or trusted domain account. SQL Server relies on Windows to authenticate the Windows user accounts.
  • Windows group. Granting access to a Windows group grants access to all Windows user logins that are members of the group.
  • SQL Server login. SQL Server stores both the username and a hash of the password in the master database, by using internal authentication methods to verify login attempts.
Server and Database Roles in SQL Server
Fixed Server Roles
  1. MS Reference: Fixed Server Roles
Fixed server roles have a fixed set of permissions and server-wide scope. They are intended for use in administering SQL Server and the permissions assigned to them cannot be changed. Logins can be assigned to fixed server roles without having a user account in a database.

Security Note:
    The sysadmin fixed server role encompasses all other roles and has unlimited scope. Do not add principals to this role unless they are highly trusted. sysadmin role members have irrevocable administrative privileges on all server databases and resources.
Fixed Database Roles
  1. MS Reference: Fixed Database Roles
Fixed database roles have a pre-defined set of permissions that are designed to allow you to easily manage groups of permissions. Members of the db_owner role can perform all configuration and maintenance activities on the database.
Database Roles and Users
  1. MS Reference: Database Roles and Users

Logins must be mapped to database user accounts in order to work with database objects. Database users can then be added to database roles, inheriting any permission sets associated with those roles. All permissions can be granted.

You must also consider the public role, the dbo user account, and the guest account when you design security for your application.

The public Role
    The public role is contained in every database, which includes system databases. It cannot be dropped and you cannot add or remove users from it. Permissions granted to the public role are inherited by all other users and roles because they belong to the public role by default. Grant public only the permissions you want all users to have.
The dbo User Account
    The dbo, or database owner, is a user account that has implied permissions to perform all activities in the database. Members of the sysadmin fixed server role are automatically mapped to dbo.
SQL Connection
Error
    DataPlus 4.0 Error
    Error connecting to SQL Server
    A network related or instance-specific error occured while establishing a connetion to SQL Server. The server was not found or was not accessible. Verify that the instance name is corrext an d that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
    Resolution
  1. Ensure that Server/Instance is correct for DataPlus Server.
  2. Ensure that SQL Server Browser Service is running.
  3. UDP port 1434 is used for SQL Server Browser. Add Inbound Rule Port UDP/1434 allow the connection
  4. Use SQL Authentication (sa:password)
Error
    DataPlus 4.0 Error
    Error connecting to SQL Server
    Cannot open database "dpDatabase", requested by the login. The login failed. Login failed for user 'DOMAIN\USER'. Login failed for user.
    Resolution
  1. Ensure that DataPlus Database is correct.
  2. Ensure the user has SQL permission to SQL database
    1. SQL Security
    2. DataPlus SQL Security
Error
    DataPlus 4.0 Error
    Error saving
    Error Saving Connection Settings: Access to the path 'C:\Program Files (x86)\Hogan Data\DataPlus 4.0\DataPlus-Connection.xml' is denied.
    Reason
    Current Windows user does not have permission to write the the folder.
    Resolution
  1. Run DataPlus 4.0 Configuration 'as administrator'
  2. Give user rights to folder (preferred method)
Error
    DataPlus 4.0 Error
    DataPlus was unable to load.
    Error getting Master Setting: Cannot open database 'dpYourDatabaseName' requested by the login. The login failed. Login for user 'DOMAIN\USER'.
    Reason
    Current Windows user does not have permission to SQL database.
    Resolution
  1. Give user permission to read SQL database
    1. SQL Security
    2. DataPlus SQL Security
Notice
    Unable to connect to database server.
    Reason Given: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40- Could not open a connection to SQL Server)
    Reason
    Server Settings/Server Name is incorrect
    Resolution
  1. Verify the Server Name and SQL Instance Name
    1. Server Name:
    2. YOURSERVER\ YOURSQLINSTANCE
Error
    DataPlus 4.0 Error
    Error connecting to SQL Server
    Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement. This could be because the pre-login handshake failed or the server was unable to repond back in time. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=21150; handshake=0;]
    Reason
    Server Settings/Server Name is incorrect
    Resolution
  1. Open Inbound port for SQL on server
   
Software Solutions
DataPlus Integration
DataPlus ERP Integration
DataPlus Custom Integration
DataPlus Addons
DataPlus Plugins & Tools
Hosting Solutions
Mobile Solutions
QuoteWerks Quoting Solutions
QuoteWerks Integration
QuoteWerks Subscription & Licensing
Hogan Data eStore
Act! CRM Solutions
CRM
CRM & Marketing Automation
Marketing Automation
Compare
Pricing
Try Growth Suite Starter
Try Growth Suite
Mobile Solutions
Helpful Links
ACTeStore.com
Act! Hosting Solutions
Act! Mobile Messenger
Act! ERP Integration
Act! Custom Integration
Act! Addons
Act! Plugins & Tools
Act! Training
Act! Resources
Act! Support
Act! Community
Act! Downloads & Updates
Learning - Support
Software Support
Support (GoToAssist)
Support Plans
Training
Act! Training
KB Hogan Data / DataPlus
KB Act Questions
KB QuoteWerks Questions
Contact Us
About Us
Contact Us
Privacy Policy
Return Policy
Resources
DataPlus Subscription
DataPlus Maintenance
QuoteWerks Maintenance
DataPlus cloud
Client Portal
Partner Portal
Partner Lead Submission
Secure CC Authorization
Secure CC Pre-Authorization

Translate this page:
©1995 - 2019 HOGAN DATA | All rights reserved