- Public Key To Generate Index Database In Excel
- Public Key To Generate Index Database In Word
- Public Key To Generate Index Database Download
- Database Index Example
Creates an asymmetric key in the database.
This feature is incompatible with database export using Data Tier Application Framework (DACFx). You must drop all asymmetric keys before exporting.
Nov 19, 2002 Generate Public key. 843810 Nov 18, 2002 7:28 PM I already have the private key on a file and I want to generate a sevrall public keys based on this private key. How to Generate a Public/Private Key Pair for Use With Solaris Secure Shell. Users must generate a public/private key pair when their site implements host-based authentication or user public-key authentication. For additional options, see the ssh-keygen(1) man page. Before You Begin.
Syntax
Arguments
asym_key_name
Is the name for the asymmetric key in the database. Asymmetric key names must comply with the rules for identifiers and must be unique within the database.
Is the name for the asymmetric key in the database. Asymmetric key names must comply with the rules for identifiers and must be unique within the database.
AUTHORIZATION database_principal_name
Specifies the owner of the asymmetric key. The owner cannot be a role or a group. If this option is omitted, the owner will be the current user.
Specifies the owner of the asymmetric key. The owner cannot be a role or a group. If this option is omitted, the owner will be the current user.
FROM asym_key_source
Specifies the source from which to load the asymmetric key pair.
Specifies the source from which to load the asymmetric key pair.
FILE = 'path_to_strong-name_file'
Specifies the path of a strong-name file from which to load the key pair. Limited to 260 characters by MAX_PATH from the Windows API.
Specifies the path of a strong-name file from which to load the key pair. Limited to 260 characters by MAX_PATH from the Windows API.
Note
This option is not available in a contained database.
EXECUTABLE FILE = 'path_to_executable_file'
Specifies the path of an assembly file from which to load the public key. Limited to 260 characters by MAX_PATH from the Windows API.
Specifies the path of an assembly file from which to load the public key. Limited to 260 characters by MAX_PATH from the Windows API.
Note
This option is not available in a contained database.
ASSEMBLY assembly_name
Specifies the name of a signed assembly that has already been loaded into the database from which to load the public key.
Specifies the name of a signed assembly that has already been loaded into the database from which to load the public key.
PROVIDER provider_name
Specifies the name of an Extensible Key Management (EKM) provider. The provider must be defined first using the CREATE PROVIDER statement. For more information about external key management, see Extensible Key Management (EKM).
Specifies the name of an Extensible Key Management (EKM) provider. The provider must be defined first using the CREATE PROVIDER statement. For more information about external key management, see Extensible Key Management (EKM).
ALGORITHM = <algorithm>
Five algorithms can be provided; RSA_4096, RSA_3072, RSA_2048, RSA_1024, and RSA_512.
Five algorithms can be provided; RSA_4096, RSA_3072, RSA_2048, RSA_1024, and RSA_512.
RSA_1024 and RSA_512 are deprecated. To use RSA_1024 or RSA_512 (not recommended) you must set the database to database compatibility level 120 or lower.
PROVIDER_KEY_NAME = 'key_name_in_provider'
Specifies the key name from the external provider.
Specifies the key name from the external provider.
CREATION_DISPOSITION = CREATE_NEW
Creates a new key on the Extensible Key Management device. PROVIDER_KEY_NAME must be used to specify key name on the device. If a key already exists on the device the statement fails with error.
Creates a new key on the Extensible Key Management device. PROVIDER_KEY_NAME must be used to specify key name on the device. If a key already exists on the device the statement fails with error.
CREATION_DISPOSITION = OPEN_EXISTING
Maps a SQL Server asymmetric key to an existing Extensible Key Management key. PROVIDER_KEY_NAME must be used to specify key name on the device. If CREATION_DISPOSITION = OPEN_EXISTING is not provided, the default is CREATE_NEW.
Maps a SQL Server asymmetric key to an existing Extensible Key Management key. PROVIDER_KEY_NAME must be used to specify key name on the device. If CREATION_DISPOSITION = OPEN_EXISTING is not provided, the default is CREATE_NEW.
ENCRYPTION BY PASSWORD = 'password'
Specifies the password with which to encrypt the private key. If this clause is not present, the private key will be encrypted with the database master key. password is a maximum of 128 characters. password must meet the Windows password policy requirements of the computer that is running the instance of SQL Server.
Specifies the password with which to encrypt the private key. If this clause is not present, the private key will be encrypted with the database master key. password is a maximum of 128 characters. password must meet the Windows password policy requirements of the computer that is running the instance of SQL Server.
Remarks
An asymmetric key is a securable entity at the database level. In its default form, this entity contains both a public key and a private key. When executed without the FROM clause, CREATE ASYMMETRIC KEY generates a new key pair. When executed with the FROM clause, CREATE ASYMMETRIC KEY imports a key pair from a file, or imports a public key from an assembly or DLL file.
By default, the private key is protected by the database master key. If no database master key has been created, a password is required to protect the private key.
The private key can be 512, 1024, or 2048 bits long.
Permissions
Requires CREATE ASYMMETRIC KEY permission on the database. If the AUTHORIZATION clause is specified, requires IMPERSONATE permission on the database principal, or ALTER permission on the application role. Only Windows logins, SQL Server logins, and application roles can own asymmetric keys. Groups and roles cannot own asymmetric keys.
Examples
A. Creating an asymmetric key
The following example creates an asymmetric key named
PacificSales09
by using the RSA_2048
algorithm, and protects the private key with a password.![Public Public](/uploads/1/2/6/1/126169176/610817600.jpg)
B. Creating an asymmetric key from a file, giving authorization to a user
The following example creates the asymmetric key
PacificSales19
from a key pair stored in a file, and assigns ownership of the asymmetric key to user Christina
. The private key is protected by the database master key, which must be created prior to creating the asymmetric key.C. Creating an asymmetric key from an EKM provider
The following example creates the asymmetric key
EKM_askey1
from a key pair stored in an Extensible Key Management provider called EKM_Provider1
, and a key on that provider called key10_user1
.See Also
ALTER ASYMMETRIC KEY (Transact-SQL)
DROP ASYMMETRIC KEY (Transact-SQL)
ASYMKEYPROPERTY (Transact-SQL)
ASYMKEY_ID (Transact-SQL)
Choose an Encryption Algorithm
Encryption Hierarchy
Extensible Key Management Using Azure Key Vault (SQL Server)
DROP ASYMMETRIC KEY (Transact-SQL)
ASYMKEYPROPERTY (Transact-SQL)
ASYMKEY_ID (Transact-SQL)
Choose an Encryption Algorithm
Encryption Hierarchy
Extensible Key Management Using Azure Key Vault (SQL Server)
A step-by-step guide for configuring DB2 UDB DPF on UNIX to use OpenSSH
Background
DB2 UDB DPF instances on UNIX require a remote shell utility to execute commands on remote nodes in the instance. For example, when starting or stopping the DPF instance (
db2start/db2stop
), the remote shell utility is used to send the startup/shutdown command to each node in the instance (regardless of whether the node is on the same host or a separate host). Further, the database administrator can choose to execute a particular command on remote nodes in the DPF instance using the db2_all or rah utilities. These utilities also require a remote shell utility. In order to use OpenSSH as the remote shell utility for your DB2 UDB DPF instance, OpenSSH must be configured such that the instance owning user ID on each host in the instance is allowed to login to each other host in the instance (as the instance owning user ID) without being prompted for any additional verification (passwords or pass phrases, for instance).This article describes how to configure OpenSSH for host-based and public key-based authentication for the AIX, Linux™, Solaris, and HP-UX operating systems. We will assume in the setup below that the DB2 UDB DPF instance is configured to run on two different hosts (there can be any number of DB2 nodes on each of these hosts), called machineA and machineB. We will also assume that the machines' fully-qualified names are machineA.my.domain.com and machineB.my.domain.com and their IP addresses, for the sake of simplicity, are 1.1.1.1 for machineA, and 1.1.1.2 for machineB. We will also assumethat OpenSSH has already been installed, that the ssh daemon process (sshd) is configured to start at system boot time, and that public and private keys have been generated for each machine.
Comparing host-based authentication to public key-based authentication
Both host-based and public key-based authentication using ssh are more secure than rsh rhost-based authentication. With ssh host-based authentication, hosts must establish a trust relationship, and then all user IDs on the configured hosts will be allowed to login as the same user ID on any other configured host. This may be convenient if you have multiple instances of DB2 running on the same set of hosts. The downside is that once any user ID on any trusted host has been compromised, that same user ID on all trusted hosts is compromised, as that user ID may now login/ssh to any other trusted host without requiring a password. (This may be a cause for concern if there are many users configured on the box, but very few of them have the need to login/ssh to other hosts without being prompted for passwords.) With public key-based authentication, only a particular user is ever configured to login/ssh to other hosts without being prompted for a password, so there is less of a security exposure. Also, you do not need to have root authority to set up public key-based authentication (portions of the host-based authentication setup require root access). Since DB2 UDB DPF instances require that the instance owner's home directory is shared across all hosts, public key-based authentication setup is fairly simple. Even though both these methods of authentication are more secure than rsh rhost-based authentication, you should choose the method that more closely matches your security requirements.
Encryption methods available in OpenSSH
OpenSSH supports both DSA (Digital Signature Algorithm) and RSA (Rivest-Shamir-Adleman) encryption. There is literature on the Internet that discusses the pros and cons of both types of encryption, and you will likely find differing views on which one is better. In the end, you should choose to use the form of encryption that meets your security requirements.The discussion below describes how to configure OpenSSH using either forms of encryption, for either type of authentication:
Whatever method of encryption and authentication you use though, ensure you are using the SSH Version 2 protocol (the default in OpenSSH 3.8p1), as the Version 2 protocol is more secure than the Version 1 protocol.
Host-based Authentication
host-based authentication using ssh allows any user ID from machineA to use ssh to login as that same user ID on machineB, assuming that the ssh client on machineA is configured to use host-based authentication and the ssh server on machineB is configured to allow host-based authentication. For your DB2 UDB DPF instance to use host-based authentication, each host in the instance must have the ssh client and ssh server configured properly. We'll start with the ssh server configuration and then proceed to the sshclient configuration.
ssh server host-based configuration
Public Key To Generate Index Database In Excel
The ssh server configuration file is sshd_config and is found in /etc/ssh for AIX, Linux, and Solaris. On HP-UX, sshd_config is found in /opt/ssh/etc. To make updates to this file and to apply the updates to the running sshd process, you will need to be the root user. Open sshd_config using your favorite editor, search for the line
# HostbasedAuthentication no
, and update the file as follows:Public Key To Generate Index Database In Word
Listing 1. sshd_config
You can remove the original line if you like, however, you might find it useful to keep the original line in the configuration file so you can see what the original configuration was and what your modifications to the configuration are.
This update to sshd_config will allow the ssh server to accept host-based authentication requests from the ssh clients. However, host-based authentication is only allowed between two trusted hosts. This trust is based on two criteria:
- The ssh server host must have an entry for the client host in the shosts.equiv file. This file is found in /etc/ssh on AIX and Linux, in /etc on Solaris, and in /opt/ssh/etc on HP-UX. If this file does not yet exist, create the file, and ensure that it is owned by the root user and only allows user read-write access and group/other read access (mode '644'). Since each host must be able to communicate with each other host in the DB2 UDB DPF instance, set up your shosts.equiv file such that it can be reused on all hosts. So, your file will look something like:
Listing 2. shosts.equiv
- The host on the ssh server system must have access to the ssh client host's public key. For host-based authentication, the trust mechanism looks for public keys in the ssh_known_hosts file, which is found in the /etc/ssh directory on AIX, Linux, and Solaris and in /opt/ssh/etc on HP-UX. If this file does not yet exist, create the file, and ensure that it is owned by the root user and only allows user read-write access and group/other read access (mode '644'). On the ssh client host, look in the /etc/ssh directory (or /opt/ssh/etc on HP-UX) for a file called ssh_host_dsa_key.pub for DSA encryption, or ssh_host_rsa_key.pub for RSA encryption, and open the file. The contents should look something like
ssh-dss <key>
for DSA encryption. orssh-rsa <key>
for RSA encryption, where<key>
will be the host's public key and is typically a very long string. Now, open the ssh server host's ssh_known_hosts file, and add the client machine's hostname (all applicable versions -- unqualified, fully qualified, and IP address, all separated by commas), followed by the contents of the ssh client host's public host key. This will look something like the following:Listing 3. ssh_known_hosts using DSA encryption
Listing 4. ssh_known_hosts using RSA encryption
When adding keys to the ssh_known_hosts file, ensure that the key is notsplit into multiple lines and that each host's entry is on a separate line.To simplify this process, you can use thessh-keyscan
utility to fetch a host's public key. The output can be appended to the ssh_known_hosts file, as shown below:Listing 5. ssh_known_hosts using DSA encryption
Listing 6. ssh_known_hosts using RSA encryption
Public Key To Generate Index Database Download
For ssh server configurations to take effect, the sshd process will need to re-process the sshd_config file. (This file is only processed when the ssh server daemon process is first started.) To re-process the sshd_config file, send the SIGHUP command to the sshd (ssh server daemon) process. This will not affect ssh sessions currently running on the host, only future sessions.
Listing 7. Determine the sshd process ID (PID)
The first entry tells us that the PID of the /usr/sbin/sshd process is 430084. To notify the sshd process to re-read the configuration file, type:
Listing 8. Restart sshd process
Listing 9. Alternative method to restart sshd process on Linux
These steps apply to all hosts in the DB2 UDB DPF instance. So the same ssh server configuration should be applied on each node. Each host should have the same copy of the sshd_config, shosts.equiv, and ssh_known_hosts files. (Remember to notify the sshd process on each host to re-read the configuration file after it has been updated.)
Once the ssh server setup above has been completed, the trust relationship between machineA and machineB is complete. Any user ID from machineA is allowed to ssh to machineB as the same user ID (this form of host-based authentication does not allow a user ID on one machine to login as a different user ID on the other machine). host-based authentication is not complete, however, until the ssh client has been configured to use host-based authentication, as well.
ssh client host-based configuration
The ssh client configuration file is called ssh_config (the ssh server configuration file is sshd_config) and is found in /etc/ssh on AIX, Linux, and Solaris and in /opt/ssh/etc on HP-UX. Open ssh_config, and look for the line
# HostbasedAuthentication no
, and update the file as follows:Listing 10. ssh_config
Then go to the bottom of the file, and add the following line:
Listing 11. ssh_config
This last line (
EnableSSHKeysign
) notifies the ssh client that it should use a separate ssh-keysign
utility to read the host's private key. (Private keys should be owned by root and should only have user read/write access.) The alternative is to make the ssh
utility itself suid root, but it's generally more secure to have ssh-keysign
suid root, since ssh
does not need to run as the root user. Before proceeding, verify that ssh-keysign
has been installed properly. On AIX, the file should be found in /usr/sbin, but it may have been installed under /usr/bin. If so, copy it over to /usr/sbin. On Red Hat Linux systems, the file should be found in /usr/libexec/openssh/, and on SUSE Linux systems, it should be found in /usr/lib[64]/ssh/. On Solaris, it should be found in /usr/libexec. On HP-UX, it should be found in /usr/lbin. Now, verify that it is marked as a suid-root executable:Listing 12. Ensure ssh-keysign is a suid-root executable
If the permissions and owner do not resemble the sample output above, then fix the permissions (on Solaris and HP-UX, it should be owned by root:bin, instead of root:system).
Listing 13. Fix permissions on ssh-keysign
The ssh client has now been configured to allow host-based authentication. The ssh client configuration should now be updated on all remaining hosts in the DB2 UDB DPF instance. Once that has been done, you can proceed to 'DB2 configuration.'
Public key-based authentication
With public key-based authentication, we will enable a single user ID (the DB2 UDB DPF instance owning user ID) to log in as that same user ID on each host in the instance without being prompted for a password. Since the DB2 UDB DPF instance owner's home directory is shared across all machines (a pre-requisite for DB2 UDB DPF instances), it is possible to complete the entire setup from a single machine. We'll use machineA in this example. Log in to machineA as the DB2 UDB DPF instance owning user ID. If this ID does not already have a ~/.ssh directory, create one now. Ensure that the .ssh directory does not allow group or other write access. At the same time, ensure that your home directory does not allow group or other write access (ssh views this as a security exposure and will not allow public key-based authentication if the directory permissions are not restrictive enough). From your ~/.ssh directory, generate a public key/private key pair:
Listing 14. Generate a key pair
Whenever prompted for input, hit Enter to accept the default value. (Ensure that no passphrase is entered, or else ssh will challenge each authentication attempt, expecting the same passphrase as a response from the user. However, DB2 does not allow remote shell utilities to prompt for additional verification.) This will generate two new files in the ~/.ssh directory, id_rsa (the private key) and id_rsa.pub (the public key) for RSA encryption, or id_dsa (the private key) and id_dsa.pub (the public key) for DSA encryption. To enable this new key pair for use with ssh, follow these steps:
Listing 15. Enable key pair
No ssh server configuration is necessary, since, by default, ssh will attempt to use public key-based authentication before password based authentication. Since no host trust relationships have been formed, the first time you ssh into a new host, you will be prompted with a message saying that the authenticity of the target host cannot be established, and you will be asked if you would like to continue. This is an extra security feature employed by ssh to ensure that your ssh communications are in fact going to the proper host (it is possible for your ssh communication to be maliciously routed to a different host in an attempt to compromise security, sometimes referred to as a 'man-in-the-middle' attack). Rather than logging in to each host from each other host, an easier way to form this trust relationship is to use the
ssh-keyscan
utility to gather the public host key for each host in the DB2 DPF instance.Listing 16. Gather RSA public host keys
Listing 17. Gather DSA public host keys
At this point, ssh public key-based authentication is now complete, and you can proceed to 'DB2 configuration.'
Configuring DB2 to use ssh as the remote shell utility
Now that you've configured ssh to use a form of authentication that does not prompt for additional verification, verify that the configuration was successful. The first test is to check if machineA can use ssh to run a simple command on machineB. Log onto machineA as the DB2 UDB DPF instance owning user ID, and type:
Listing 18. Simple ssh configuration test
If you are prompted for a pass phrase, it likely means you are using public key authentication and that you specified a pass phrase when generating the public key/private key pair. Go back to the public key authentication instructions, and generate a new public key/private key pair that does not require a pass phrase. If you are prompted for a password or receive any other errors, proceed to 'Troubleshooting.' If the command takes longer than a second or two to return, there may be either network name resolution configuration problems or configuration issues with your particular version of ssh. In either case, search the Internet and your network configuration documentation for possible causes and solutions. Otherwise, it's now time to notify DB2 to start using ssh to execute remote commands. From the DB2 UDB DPF instance owning user ID on machineA, type:
Listing 19. Notify DB2 to start using ssh
This notifies DB2 which remote shell utility to use when executing both internal DB2 commands on remote DB2 nodes (for instance, starting a remote DB2 node using
db2start
) and when running commands on all DB2 nodes through the db2_all
script. To test if ssh has been configured properly on all hosts in the DB2 UDB DPF instance, try a simple command -- have each DB2 node echo a response back, using the following command:Listing 20. Echo hi once on each DB2 node
If this command completes successfully without prompting for additional verification, then we're ready for the next test. Otherwise, skip 'Troubleshooting.' The last test ensured that the current DB2 node can ssh into each other DB2 node. You also want to ensure that any DB2 node can ssh into any other DB2 node. So now run a modified version of the last command:
Listing 21. Request each node to echo hi on each other node
There should be many more output lines this time. As long as all DB2 nodes responded without being prompted for additional verification, DB2 is now fully configured to use ssh as the remote shell utility. You will need to stop the database server (
db2stop
) and restart the server (db2start
) for this new setting to take effect on the DB2 server (the setting takes effect immediately for the db2_all
script).Troubleshooting
There could be many reasons why host-based or public key-based authentication is not working properly -- each version of ssh seems to make some minor changes here and there that end up breaking some configurations. This section will not go into extensive detail on how to troubleshoot all ssh configuration problems but will get you started. Before proceeding, re-read all the above directions, and ensure that each step of the configuration has been followed. There are a couple of common things you should check for:
- Ensure that all ssh server configuration files refer to the canonical nameof the client, as seen by the server. For instance, if the ssh client connection comes from IP 1.1.1.1, a gethostbyname(1.1.1.1) on the ssh server should be able to map that to machineA (or machineA.my.domain.com). Consult your network configuration documentation for more details.
- Check permissions carefully. Private host keys should be owned by root and only readable/writeable by root. Public host keys should be readable/writeable by root, but only readable for group/other access. For public key-based authentication, a user's private key should be owned by that user, readable/writeable by that user, and not readable by anyone else. The public key should be readable/writeable by the owner and only readable forgroup/other access. Also for public key-based authentication, the DB2 UDB DPF instance owning user ID's home directory should not be writeable by group/other. The ~/.ssh directory should only be readable/writeable by the instance owning ID (no group/other access should be allowed).
If everything seems to be configured properly, then it's time to take some ssh server and ssh client traces and dig through the traces to diagnose the problem. To do this, you'll need to pick two hosts that do not allow the desired form of authentication (we'll assume machineA and machineB in the discussion below). Log in as root on machineA. Start the ssh server on a specified port with extra debugging diagnostics enabled as follows. (We'll use port 5555 for this example; any available user port will work):
Listing 22. Start a new diagnostic server session
Then, log in as the DB2 UDB DPF instance owning user ID on machineB, and start the ssh client as follows:
Listing 23. Start a new diagnostic client session
Enter your password, if prompted, and this command will execute to completion. (You will be prompted for your password as a last resort with ssh. By the time you are prompted for a password, the desired form of authentication has already failed.) Now the fun detective work can begin. Open up the client side trace first (ssh.trace on machineB) and look through the messages for something that looks like a failure. There's a lot of information in there, so it may take time to decipher it. From the client trace, you should be able to determine whether it's the ssh client or the ssh server that is disallowing your desired form of authentication. If the server is disallowing the authentication, open up the ssh server trace (sshd.trace on machineA) to look for clues. When you find entries in either trace that do not look correct oryou do not understand, the easiest way to decipher the message is to do an Internet search on the exact string (or portion of the string) that you found in the trace. Chances are someone has already run into a similar configuration problem, and they'll hopefully have the solution you're looking for.