1st communication
Good Afternoon Customer,
Company elected for a communal hosted environment vs an isolated Dedicated. For security purposes, direct access to SQL is not available in this setup structure. What can be provided in the communal space is one of the following:
- Elevating permissions for a server (1st login) to access the SQL data with Full Desktop privileges and read-only capacity in SQL for SSMS use from the App Server
- Creation of a SQL read-only user for use with SSMS, Access, or other custom reporting [e.g. Excel] from the App server
- Daily database Uploads to an Azure or compatible storage solution for your team to download and perform operations on a separate data warehouse or virtual environment
If your team needs direct ODBC access to the SQL data from any external source beyond the hosted Application Server, this would require a Dedicated environment, isolated with its own Domain, App, and Web servers.
Note: Read-only SQL queries can still impact front-end SedonaOffice performance on the app server; any subsequent resource increase evaluations may necessitate a Dedicated environment as well if normal operation is impacted and unable to be managed within the current AWS instance class.
Let us know if you would be available for a call to discuss further if one of the options above does not align with your plans to discuss alternatives.
Option 1: Elevating permissions for a server (1st login) to access the SQL data with Full Desktop privileges and read-only capacity in SQL for SSMS use from the App Server
Action
Response
Option 2: Creation of a SQL read-only user for use with SSMS, Access, or other custom reporting [e.g. Excel] from the App server
Action
Update the following script and run against TSP/App server SQL Instance and Main SQL Instance to create the SQL User. The 3rd commented line provides the Connection string example for the customer.
-- Create new user in instance with read only access to all databases typically for reporting
-- Run Against Instance CHANGEME to USER and Change CHANGEPASSWORD to Generated PW
-- Provide info to customer DRIVER=SQL Server Native Client 11.0;SERVER=[SQL SERVER];UID=CHANGEME;;APP=Microsoft Office;WSID=[APP SERVER];DATABASE=[Production DB];
USE [master]
GO
CREATE LOGIN [CHANGEME] WITH PASSWORD='CHANGEPASSWORD', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO
Use master
GO
DECLARE @dbname VARCHAR(50)
DECLARE @statement NVARCHAR(max)
DECLARE db_cursor CURSOR
LOCAL FAST_FORWARD
FOR
SELECT name
FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb','distribution')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @statement = 'use '+@dbname +';'+ 'CREATE USER [CHANGEME]
FOR LOGIN [CHANGEME]; EXEC sp_addrolemember N''db_datareader'',
[CHANGEME];EXEC sp_addrolemember N''db_datawriter'', [CHANGEME]'
exec sp_executesql @statement
FETCH NEXT FROM db_cursor INTO @dbname
END
CLOSE db_cursor
DEALLOCATE db_cursor
Response
**Update ALL Highlighted fields**
Good Day Customer,
We have created a separate SQL read-only user for SSMS/Report use going forward; the credentials are below. We have also provided an example connection string for your production database as a guide. This user/credential is only functional from the Hosted Application Server.
**Please note that reporting and queries [even read-only] performed during your team's SedonaOffice production/business hours can still impact performance and cause timeouts for production work for your team. Use of the SQL user and/or subsequent reporting that leads to query-timeouts, front-end performance issues, or results in needing further custom datawork/reporting requires a billable project for review, as they are out of normal support scope.
SQL : SQLAWSPAC1\ADVSECSYSSQL
UID : AdvSecSysReports
PW : GENERATE_NEW_PW
WSID : APPSERVERNAME
Example MS Connection String
DRIVER=SQL Server Native Client 11.0;SERVER=SQLAWSPAC1\ADVSECSYSSQL;UID=AdvSecSysReports;;APP=Microsoft Office;WSID=AdvSecSys;DATABASE=AdvSecSys_Final;
Regards,
BoldGroup Support
Phone: (719) 593-2829 Fax: (719) 213-2502
4050 Lee Vance Drive, Suite 250. Colorado Springs, CO 80918
The information contained in this communication from the sender is confidential. It is intended solely for use by the recipient and others authorized to receive it. If you are not the recipient, you are hereby notified that any disclosure, copying, distribution or taking action in relation of the contents of this information is strictly prohibited and may be unlawful. If you have received this email in error, please immediately notify the sender by reply e-mail and destroy all copies of the communication and any attachments.
Option 3: Daily database Uploads to an Azure or compatible storage solution for your team to download and perform operations on a separate data warehouse or virtual environment
Action
Response
