Privileges Needed to Backup Databases

Leave a comment

Creating backups are one of the most vital tasks in maintaining computer infrastructure. System administrators and Database Administrators in particular get saddled with this unglamorous task. Backing up an operating system (OS) generally does not require any special manipulation of the OS prior to sending OS files to a backup target such as tape, disk or deduplicated disk targets like Data Domain. A system administrator can do things the old fashioned way such as generating a tar file and sending it to the backup target. Or he or she can deploy sophisticated backup software such as EMC’s Avamar to administrate and automate the backup process across hundreds or thousands of computers.

In any event, backing up the OS generally requires the root or Administrator level privilege. And this is rarely a problem because the System Administration teams runs the backups, and those users typically have that privilege anyway.

Backing up databases presents a variety of challenges. One is that for many database systems the Database Administrator (DBA) must place the database in a special mode prior to creating the backup. Another is that on many popular database systems, the DBA must backup files that store the record of transactions since these are used to permit recovery to any point in time. Such files are known as transaction logs on SQL Server, Sybase, Postgres and DB2. They are called archive logs in Oracle, although older Oracle documents called them offline redo logs.

And another challenge is that the privilege needed to backup a database varies among different database vendors. If the DBA team is running the backups, the level of privilege needed within the database is not a problem. But if the database backups are getting run by another team, then the DBA team may be reluctant to give out a high level of privilege in order to backups. What to do? To date, there is not much flexibility in the privilege needed to do the database backup.

Here’s a table spelling out backup privileges needed by an internal database user running a backup.

DATABASE PRIVILEGES NEEDED BY DATABASE USER RUNNING THE DATABASE BACKUP OTHER CONSIDERATIONS
ORACLE The database user executing the RMAN backup needs the SYSDBA role. The users SYS and SYSTEM have this role. Or a new user can be created, but it must have SYSDBA.
If using an RMAN catalog, the database user that connects to the RMAN catalog does NOT have to have SYSDBA. It does need the RECOVERY_CATALOG_OWNER role. In other words, one use can run the backup and another user can record the activity in the RMAN catalog.

And what about Oracle 12C? It’s possible that the privilege needed to backup the new pluggable database model will no longer need SYSDBA. I’ll update this post when 12C gets released.

SQL SERVER For an individual database, a SQL Server database user with the database level role db_backupoperator can perform a backup upon that database. NOTE that this privilege must be granted within each new database that gets added to the instance. The server level role sysadmin will allow a user to backup any database, even new ones. While db_backupoperator is much lower privileged than sysadmin, only sysadmin insures that new databases will get backed up. Using db_backupoperator requires explicitly granting the privilege on each new database.
SYBASE ASE The lowest level of privilege needed is the Operator role. Operator will permit backup and restore on any database in the instance.

The System Administrator (SA) privilege also provides this, but this is the highest level role.

Finally, database owner can do backup and restore, but this privilege must be granted individually for each database.

The Operator role will work with new databases, so it is a good choice.
DB2 You must have SYSADM, SYSCTRL, or SYSMAINT authority to use the backup and recover utilities in DB2. SYSADM is the most powerful role of the 3. SYSCTRL controls operations that affect system resources, but cannot update table data. SYSMAINT can perform maintenance operations on all individual databases within an instance, but like SYSCTRL, cannot update table data. SYSCTLR and SYSMAINT both offer a lower privileged account than SYSADM.

NOTE: I plan to add to the above table as I get more information on databases.

SQL Server calling Oracle, Can you hear me?

Leave a comment

This is the last post from my 2010 archives. I’m glad to pop these up on to my blog, if only to be able to reference them quickly if I need to mess around with any of these features. Now I’ve got to get cracking and generate some new content!

This is the 2nd blog posting addressing querying SQL Server from Oracle and vice versa. This post covers the vice versa part, from SQL Server to Oracle.

Assuming that SQL Server is working fine on your windows host, the first step is to install the Oracle client on the SQL Server host. Be sure to choose the Windows components, specifically Oracle OLEDB. That’s critical! Then configure the Oracle networking files appropriately for your target system. By this I mean adjusting the tnsnames.ora file if you are hard coding the Oracle database location, or ensuring that you have the correct LDAP.ORA setting if you use OID to look up Oracle locations. Here are some links for help with the if you have not configured Oracle client network files: 11G R2 docs on adjusting tnsnames using Oracle GUI tools, Ora FAQ on editing tnsnames.ora directly, Ora FAQ on editing ldap.ora directly.

Once the Oracle client is correctly configured, try a quick test with TNSPING from the command line to ensure that Oracle client can find the database with the tnsnames.ora or ldap.ora changes you made.

If things are OK at the Oracle client level, you must now restart SQL Server. This is because the Oracle client install updates the system PATH environment variable. The SQL Server service only checks this at startup time, so it needs a restart to get the updated value of PATH in order to find the Oracle client files.

The rest of this article addresses how to enable SQL queries executing within SQL Server to contact the Oracle database using something called a Linked Server. However, know that frequently people make Oracle data available in SQL Server by means of an SSIS job that queries Oracle and loads data into a table. By making a linked server, any sql session with appropriate permissions will be able to use the linked server.

You can create the linked server in T-SQL like this:

EXEC master.dbo.sp_addlinkedserver @server = 'ORA_LINKED_S', @srvproduct='Any string', @provider='OraOLEDB.Oracle', @datasrc='ORA_SID', @provstr='XXX'

In my testing, for Oracle data sources, it seemed that only the provider argument had to be precise in order to specify the OLEDB Oracle driver and the datasrc argument had to match the Oracle name as indicated in TNSNAMES.ORA or your LDAP server. The other values are descriptive for Oracle sources.

Then you need to provide the Oracle login information:

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname='ORA_LINKED_S',@useself='False',@locallogin=NULL,@rmtuser='joe_user',@rmtpassword='joe_pwd'

You can also configure a Linked Server via the SQL Server Management Studio GUI. To do that, connect to your instance with Management Studio, then expand the Instance and then the Server Objects icon. Right click on the Linked Servers icon and select New Linked Server choice. This screen will allow you to input the values needed. Choose the Oracle Provider for OLEDB. You also need to provide the username and password for the user connecting to Oracle.

With the linked server in place, you can now query Oracle from within SQL Server.

Here again you have choices.

To query using native T-SQL, use this syntax:

select * from ORA_LINKED_S..SCHEMA.TABLE_NAME  

Yes, that’s two dots before the word SCHEMA. In my testing, I had to do all upper case in order for the query to run. It’s possible if the remote Oracle objects were created with mixed case using quotations that you would have to match that case.

The other choice is to do pass through queries, which enable you to use Oracle SQL syntax that gets parsed at the remote Oracle database. You use the OPENQUERY function to do that.

select * from openquery(ORA_LINKED_S, 'select * from table_name where rownum < 10')

Note that in the above example the case was not important, provided the objects were not created with quotation marks and specific case in Oracle. Also, the above uses an Oracle specific syntax: rownum < 10. That rownum clause won't work in native T-SQL.

Here's a gotcha that we encountered in my shop when having 64-bit SQL Server 2008 running on 64-bit windows and running SSIS jobs that connected to a remote Oracle database. The solution to getting everything working was to install both 32 and 64 bit Oracle clients on the same machine. See this write up from sqlblogcasts.com for more details.

Oracle calling SQL Server, do you read me?

Leave a comment

Also from 2010 archives: two posts on hetergenous queries using SQL Server and Oracle. I’d hoped to do a 3rd on general issues that arise when keeping data in a remote system, but haven’t produced it yet…

Querying SQL Server from within Oracle and vice-versa is a messy little corner of the database world. . This post will address the setup needed to query SQL Server from Oracle. The next will approach querying Oracle from SQL Server.

Notes on configuring an Oracle database to query a remote SQL Server database follow. For more detailed information, see the Hetergeneous Connectivity section of the Oracle database documentation. It’s important to know that you can either use the ODBC support that comes with the basic Oracle database license for connecting to 3rd party databases. Or you can pay extra for a specific gateway tailored for your target 3rd party database. The Oracle Database Gateway for ODBC User’s guide, discusses specifics about exactly what support is provided with the default no extra cost gateway. This article discusses the no extra cost ODBC gateway.

Note that the no extra cost option that ships with 11G is called DG4ODBC (database gateway for ODBC). It replaces the HSODBC (heterogeneous services ODBC) program that shipped with 10G and earlier. Also note that I was unable to get DG4ODBC working with Oracle DB release 11.1.0.6.0 on Windows. When I upgraded to 11.1.0.7.0, DG4ODBC worked fine.

Connecting to SQL Server from an Oracle database is easier when the Oracle system runs on Windows. That’s because SQL Server client software must be installed on the Oracle host, as well as an ODBC driver for SQL Server. Installing both is straightforward on Windows. Simply install SQL Server client using the file sqlncli.msi, or run the SQL Server database installer and just select the SQL client choices. Doing either takes care of both the native connectivity and the ODBC driver. On unix you will either have to purchase a commercial ODBC driver for SQL Server which comes bundled with the native connectivity like Data Direct or use some of the open source offerings such as Free TDS for the native connectivity and Unix ODBC for the odbc layer.

After you put the SQL Server connectivity software in place, you then need to create an ODBC data source to your remote SQL Server. On windows, this is done via Control Panel – Administrator Tools – Data Sources (ODBC). On unix, you will be configuring text files specific to the odbc driver software you have installed.

At this point, you can verify if connectivity from your host to SQL Server is working without using Oracle. For example on Windows, click the Test Data Source button within the ODBC DSN set up screen. And on unix, there likely will be a command line utility with which you can test connectivity.

With connectivity in place, the next steps are about describing to Oracle your ODBC data source. These steps will be the same on unix and windows since they take place at the Oracle level.

The first part of describing to Oracle your ODBC source is specifying a database gateway. As stated above, we will illustrate using the no extra cost ODBC gateway. You create an initDG4ODBC.ora file that gets put in the $ORACLE_HOME/hs/admin directory. You’ll need these 3 parameters at a minimum:

# Substitute MYDATASOURCE with whatever you called the SQL Server in your ODBC entry.
HS_FDS_CONNECT_INFO = MYDATASOURCE
HS_FDS_TRACE_LEVEL = off
HS_FDS_SUPPORT_STATISTICS=FALSE

There is another step to telling Oracle about your remote SQL Server. You must create an entry in the listener.ora file. It will reference the initDG4ODBC.ora file. The program name will be DG4ODBC.

(SID_DESC =
(PROGRAM = DG4ODBC)
(SID_NAME = DG4ODBC)
(ORACLE_HOME = C:\oracle\product\11.1.0\db_1)
)

Once you’ve installed SQL Server client, ODBC driver and configured the $ORACLE_HOME/hs/network\initDG4ODBC.ora and listener.ora files, you are ready to create a database link that connects to the remote SQL Server. This link can then be referenced by SQL statements to get data.

CREATE public DATABASE LINK MYDATASOURCE
CONNECT TO "user" IDENTIFIED BY "pwd" USING 'DG4ODBC';

Querying against this link immediately creates case-sensitivity issues. SQL Server will need the correct case for the fields and tables that you query. For example the query below generates an error.

SQL> select name from sysobjects@MYDATASOURCE;
select name from sysobjects@MYDATASOURCE
       *
ERROR at line 1:
ORA-00904: "NAME": invalid identifier

But this gets the case correct:

select "name" from sysobjects

You can use Oracle SQL functionality provided the DGODBC can convert it successfully.

For example, this will work:

SQL> select column_name from all_tab_columns@MYDATASOURCE where table_name = 'TBLSTATS';

Oracle has mapped its data dictionary view to the native SQL Server one.

You can also send SQL statements directly to the remote SQL Server without having them checked in Oracle first using the DBMS_HS_PASSTHROUGH pl/sql API. This means your syntax can be in SQL Server T-SQL syntax. You can execute a subset of DDL statements with the DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE command.

conversions part 2: ip addresses stored as decimal

Leave a comment

My previous conversion post covered unix epoch date conversion. Here I discuss my adventures with converting decimal ip addresses.

Internet IP v 4 addresses look like this 41.22.154.123. 4 numbers separated by periods. Each number ranges from 0 to 255. I never knew it, but these addresses are commonly stored as binary or as decimal numbers. One reason is to facilitate sorting the addresses. There’s no good way to sort the addresses in the dotted notation.

Here’s the function I ended up using with Oracle PL/SQL to convert the decimal value to dotted.

create or replacefunction inttoip(ip_address integer) return varchar2
deterministic
is
begin    
return to_char(mod(trunc(ip_address/256/256/256),256)) ||'.'||to_char(mod(trunc(ip_address/256/256),256))           ||'.'||to_char(mod(trunc(ip_address/256),256))           ||'.'||to_char(mod(ip_address,256));
end;

A colleague sent to me some SQL Server and Oracle conversions using a slightly different approach. I’ve copied these below. And of course, google searches will quickly direct you to other material in this area.

-- MS SQL Server with convert to bigint
SELECT dbo.IPADDRESS.IPADDRESS,
CAST(ROUND( (cast(dbo.IPADDRESS.IPADDRESS as bigint) / 16777216 ), 0, 1) AS varchar(4)) + '.' +
CAST((ROUND( (cast(dbo.IPADDRESS.IPADDRESS as bigint) / 65536 ), 0, 1) % 256) AS varchar(4)) + '.' +
CAST((ROUND( (cast(dbo.IPADDRESS.IPADDRESS as bigint) / 256 ), 0, 1) % 256) AS varchar(4)) + '.' + 
CAST((cast(dbo.IPADDRESS.IPADDRESS as bigint) % 256 ) AS varchar(4)) as IPDottedNotation
FROM  dbo.IPADDRESS

-- MS SQL Server From non numeric datatype
 
SELECT     dbo.DEVICE.HOSTNAME, dbo.IPADDRESS.IPADDRESS,
CAST( ROUND( (dbo.IPADDRESS.IPADDRESS / 16777216 ), 0, 1) AS varchar(4)) + '.' +
CAST( (ROUND( (dbo.IPADDRESS.IPADDRESS / 65536 ), 0, 1) % 256) AS varchar(4)) + '.' +
CAST( (ROUND( (dbo.IPADDRESS.IPADDRESS / 256 ), 0, 1) % 256) AS varchar(4)) + '.' + 
CAST( (dbo.IPADDRESS.IPADDRESS % 256 ) AS varchar(4)) as IPDottedNotation
FROM dbo.IPADDRESS 

-- Oracle PL/SQL
SELECT DEVICE.HOSTNAME, IPADDRESS.IPADDRESS,
cast(
CAST(ROUND((IPADDRESS.IPADDRESS / 16777215 ), 0) AS varchar2(3)) || '.' ||
CAST(MOD(ROUND((IPADDRESS.IPADDRESS / 65535), 0), 256) AS varchar2(3)) || '.' ||
CAST(MOD(ROUND((IPADDRESS.IPADDRESS / 256  ), 0), 256) AS varchar2(3)) || '.' ||
CAST(MOD(IPADDRESS.IPADDRESS, 256 ) AS varchar2(3))
as char(20)) as IPDottedNotation
FROM IPADDRESS
 

conversions from unix epoch format OR loving the 1970s

Leave a comment

I recently encountered two conversion issues that are specific to working with computers. I address converting unix epoch date format to human readable date format here. My next post will address converting IP addresses stored as decimal integers to the more familiar dotted format.

There’s a popular work request management software product called Teamtrack from Serena software. The version deployed in my shop stores dates in its database using unix epoch format, which is the number of seconds since Jan 1, 1970. To convert this in a sql server t-sql query, I used:

select dateadd(s, table.date_field, '19700101') as friendlydate
from table

Interestingly, there were a few fields where I needed to do a slight modification, and I’m not sure why. Perhaps another timezone?

select dateadd(s, table.date_field,  '19691231 19:00:00') as friendlydate
from table

Forum poster jciappas from Brazil posts the Oracle PL/SQL version.

And at a dedicated web site for epoch converters, you can find code for doing the conversions in a variety of languages and databases. This web site covers going back and forth from unix epoch to human readable.

dtutil, named instance and package import

1 Comment

I was trying to get an SSIS package imported into a named instance using the command line utility dtutil and couldn’t get it to work.

This command had been working just fine with a default instance.
dtutil /File userdbbackup.dtsx /DestServer hostname /Copy DTS;"MSDB\Maintenance Plansuser\dbbackup"

But when running the same command on a named instance, I got the following:
dtutil /File userdbbackup.dtsx /DestServer hostnameinstancename /Copy DTS;"MSDB\Maintenance Plans\userdbbackup"

Microsoft (R) SQL Server SSIS Package Utilities
Version 10.0.1600.22 for 32-bit
Copyright (C) Microsoft Corp 1984-2004. All rights reserved.

Could not create SSIS Service Storage object because of error 0x800706BA.
Error (0x800706BA) while checking for the existence of package “Maintenance Plansuserdbbackup” in the SSIS package store.
Could not create SSIS Service Storage object because of error 0x800706BA.
Description: The RPC server is unavailable.

The solution was changing the DTS flag to SQL in the /COPY parameter. I also had to change the folder location by removing MSDB.
dtutil /File userdbbackup.dtsx /DestServer hostname /Copy SQL;"\Maintenance Plans\userdbbackup"

This means the package is getting stored within MSDB in the SQL Server rather than the SSIS Package Store.

Note that the commands above do not specify username and password. The commands were run on the local host with a privileged user that had a trusted connection to the DB.

Here are some links on the topic of getting the SSIS Package Store to work with Named Instances:  Robert Davis explains and Tim Cullen covers similar ground.

If I understand the above links correctly, if I manipulate the XML files as they indicate, the dtutil /COPY DTS can be made to work with named instances.

By the way, this concise dtutil tutorial helped steer me in the right direction. It even mentioned dtexec and dtexecui, both new to me.

SQL Server connection protocols and named instances

Leave a comment

I’ve been working on a project that involves automating tasks on SQL Server installs. Things got a bit messy when working with multiple instances on the same host.

To automate sql scripts via a VB.NET program, I ended up using shared memory connections. The code will always run locally on the host where the instances reside, so no problem there. I learned the following about SQL Server connection string behavior:

1. This will always use the default instance with a shared memory connection. Parentheses are required.
Data Source=(local); Integrated Security=True

2. This will allow a connection to a named instance via shared memory. Note there are no parentheses.
Data Source=HOSTNAME\NAMEDINSTANCE; Integrated Security=True

3. This will force a connection over TCP/IP on the port specified, provided the SQL Server Network Configuration has been set to the port indicated.
Data Source=HOSTNAME\NAMEDINSTANCE,port; Integrated Security=True

The above connection strings can be used in data access code in VB.NET and other languages.

Want to force a shared memory connection from sqlcmd? You can append the prefix lpc:
sqlcmd -S lpc:HOSTNAME\NAMEDINSTANCE -E

Although
sqlcmd -S HOSTNAME\NAMEDINSTANCE -E
does the same thing when executed from the local host. When executed remotely, you likely will need the port to appended after NamedInstance.

For other SQL Server protocols such as Named Pipes, there must be additional abbreviations, but I haven’t dug into that…

Lastly, one can tell from within SQL Server what protocols are used by what sessions. The view sys.dm_exec_connections shows that, specifically the net_transport column. See the below query which joins to sysprocesses.

select d.net_transport, s.spid, s.program_name, s.nt_username, s.hostprocess, s.lastwaittype, d.client_tcp_port, d.protocol_type, d.protocol_version
from sysprocesses s
inner join sys.dm_exec_connections d
on s.spid = d.session_id

Older Entries