I am looking for a way to install keyring_aws plugin in mysql community edition. Is it possible? I gone through couple of docs, > https://dev.mysql.com/doc/refman/5.7/en/keyring-aws-plugin.html > https://dev.mysql.com/doc/refman/5.7/en/keyring-installation.html And also tried to findout keyring_aws.so file, but couldn't find it anywhere. I checked in mysql dir also. root@server:~# ls -l /usr/lib/mysql/plugin/ total 644 -rw-r--r-- 1 root root 21224 Jan 22 17:26 adt_null.so -rw-r--r-- 1 root root 6288 Jan 22 17:26 auth_socket.so -rw-r--r-- 1 root root 44144 Jan 22 17:26 connection_control.so -rw-r--r-- 1 root root 108696 Jan 22 17:26 innodb_engine.so -rw-r--r-- 1 root root 88608 Jan 22 17:26 keyring_file.so -rw-r--r-- 1 root root 154592 Jan 22 17:26 libmemcached.so -rw-r--r-- 1 root root 9848 Jan 22 17:26 locking_service.so -rw-r--r-- 1 root root 10840 Jan 22 17:26 mypluglib.so -rw-r--r-- 1 root root 6288 Jan 22 17:26 mysql_no_login.so -rw-r--r-- 1 root root 56064 Jan 22 17:26 rewriter.so -rw-r--r-- 1 root root 56936 Jan 22 17:26 semisync_master.so -rw-r--r-- 1 root root 14768 Jan 22 17:26 semisync_slave.so -rw-r--r-- 1 root root 27568 Jan 22 17:26 validate_password.so -rw-r--r-- 1 root root 31296 Jan 22 17:26 version_token.so Can we build this .so file from scratch? If yes, where can I find the source?

A tutorial is asking me to encrypt passwords using SHA512 in the following manner: INSERT INTO vir_users (id, domain_id, password , email) VALUES ('1', '1', ENCRYPT('pw1', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16))), 'email1@example.com'), ('2', '1', ENCRYPT('pw2', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16))), 'email2@example.com'); How would I replace this with a MySQL 5.7 non-deprecated AES_ENCRYPT instead of the old ENCRYPT way of doing things? MySQL's documentation is severely lacking in this case at it does NOT provide any clear examples. I found a way of doing it outside the DBMS, however, I would like to keep it all contained and not dependent on external code and/or tools; that's why would like to know. When I run this query now, I get this error message: >'ENCRYPT' is deprecated and will be removed in a future release. Please use AES_ENCRYPT instead **update:** The reason it's using this is that I need to store the password in such a way that Dovecot's default_pass_scheme (which is set to SHA512-CRYPT) can validate my passwords. The $6$ part of the passwords seems to be important.. I don't know why, but all passwords that are generated the old way start with that. Please check the tutorial on why this is.

So basically we are designing an application to store PII patient data, we are currently storing by encrypting the data in AWS RDS using an KMS key, what i mean by that is encyrpting the actual data itself in the columns. Can anyone suggest a more better (less expensive) and efficient way of storing PII data. Is the way i am currently doing it the correct way?

I’m currently working on a proof of concept (POC) involving column-level encryption and key management (SMK/DMK) in a SQL Server 2019 Always On Availability Group (AG) environment (CU20). I’ve tested multiple scenarios and am trying to determine the best approach for ensuring encryption consistency across replicas. In my first test, I created the Database Master Key (DMK) before adding the database to the AG. Everything worked fine — the encrypted data was accessible from the readable secondary and even after failover. In another test, I added the DMK after the database was already in the AG. In this case, encrypted data was not accessible on the readable secondary or after failover. To fix this, I removed the database from the AG and re-added it with the password — this allowed encryption to work across all replicas. In a more complex scenario, I created a DMK without explicitly encrypting it with the local Service Master Key (SMK) on the primary. After a failover, the readable secondary (which became the new primary) couldn’t access encrypted data. To resolve this, I ran OPEN MASTER KEY DECRYPTION BY PASSWORD followed by ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY on the new primary. This fixed the issue on the new primary, but now the original secondary (i.e., the old primary) couldn’t access encrypted data. I noticed that after restarting the servers, only the latest SMK used for DMK encryption allows automatic decryption, and the previous SMK is no longer valid unless re-added. Given this behavior, I have a few questions: * Is it expected that only one SMK can be used for automatic decryption of a DMK at any time? * Should DBAs routinely re-encrypt the DMK with the local SMK after every failover to ensure continuity? * Would it be better to restore the same SMK across all replicas to maintain consistency — and is that considered safe in a production environment? I’m aware that SMK restore using FORCE can affect linked servers, credentials, and SSIS, so I’m hesitant to use it in prod without clarity. I’d really appreciate input on the best practices or any Microsoft-recommended approach for managing encryption chains in AG setups with minimal risk.

SQL Server 2022, CU 10 on Windows 2022. I am migrating a SQL Server 2019 database to a new SQL Server 2022 and when attempting to SELECT rows from an external Polybase driven table, I get the following error: The service master key on this server is a restored copy of the service master key from the original server. I verified that information by comparing the GUID of the ##MS_ServiceMasterKey## in the master database on both instances. The External Table is accessible on the original server. If the service master key in the master database is the same as the key in the original server, what other steps do I need to take? The password that was used to create the **database** master key is no longer available. Polybase is enabled for the instance and the Engine and Data Movement services are running. Thank you, Craig

As I understand **ProxySQL** has support for *SSL encryption* for inbound and outbound traffic (front/backend) and supports *password hashing*. However back in 2017, one had direct database access: https://dba.stackexchange.com/questions/164705/how-to-easily-bring-80-mysql-users-into-proxysql/212991 According to *Icarus*'s answer, it would be rather easy for someone who broke into the hosting server to gain access to all endpoints listed on this database by making a select call directly to it. Is there any way of encrypting the SQLite3 DB? Or perhaps of encrypting the data stored in the database (similar to how SSL is used for in-transit data)? Basically looking for *data-at-rest* securing strategies. Perhaps this is already implemented in recent versions? I could not find any documentation supporting it, but it did seem that may be the case: https://github.com/sysown/proxysql/blob/v2.0.13/lib/ProxySQL_Admin.cpp Which, if I understood correctly implements SHA1 encryption: https://stackoverflow.com/questions/3179021/sha1-hashing-in-sqlite-how If this is correct, is this restricted to password hashing? Or is it part of the SSL encryption implementation? Or is there actually a way of enabling encrypted "DISK" read/writes? Is this by any chance implemented by default on v2+ builds? Ultimately, I plan on encrypting the volume this is installed in, and have a second proxy layer so that I don't expose endpoint information; this would be an additional layer of protection. Thanks, I appreciate any insights you may have!

I've got my standard MariaDB database with phpMyAdmin. I'm working with OpenSSL and php. And I've got nothing more than a basic rented web-server. $cipher_algo = 'aes-128-cbc'; $key = '2IZVjWzP5jebqU2LtGdQsyM937OIOdvY'; $iv_length = openssl_cipher_iv_length($cipher_algo); $iv = openssl_random_pseudo_bytes($iv_length); $options = 0; //base 64 encoded string $toCipher = "text to crack"; $encrypted = openssl_encrypt($toCipher, $cipher_algo, $key, $iv_length, $iv); $deCrypted = openssl_decrypt($encrypted, $cipher_algo, $key, $iv_length, $iv); 1) - I've got my text that I encrypt: the encrypted text. 2) - I've got the Initialisation Vector (IV) that I also store in a DB. 3) - The Key to (de)crypt the text with the IV. The Key: I can store that in an .environment file above the server root. I don't want to put the encrypted text and the IV next to each other in the same table, for obvious reasons. So I'm thinking with the means that I have: a different Database, in unfortunately the same server. BUT: I can use an elaborate unique value to link the IV to the encrypted text. And I could encrypt these unique values so that they don't appear the same when you place the tables next to each other. But if the database is hacked and somebody is looking through it. Than who cares how magical and unique these values are. Entry 1 in the first database probably links up to entry 1 on the second database. And entry 2 in DB1 with entry 2 in DB2. and so on. So what is a propper way to mismatch entry fields in different tables/databases? OR, what are my other options?

I have a development machine that has two drives: C:\ - Operating system (Win 10 Ent) and SQL Server 2012 Standard installation D:\ - SQL Server databases, backups, logs I recently activated BitLocker on both drives, then activated auto-unlock because I realised that SQL Server can't access the databases unless the drive is unlocked. Now with SQL (and only SQL, all other services/programs are fine), on an **intermittent** basis I see the following behaviours occur: 1. After waking from sleep mode, and then starting SSMS, I cannot log in (login fails with invalid credentials error). This occurs for the Windows login account only 2. If login succeeds, or if I login using the **sa** account, then all databases are in the Pending Restore state I guess both of these are due to the drive becoming locked during sleep (or perhaps not?)? After waking from sleep, if I restart the SQL Server service, and then restart SSMS, everything works fine. I wondered what might cause this to be intermittent please, or if my assumptions are wrong, what is actually causing this? *PS. Perhaps a BitLocker tag might be useful, as I see there are plenty of other questions relating to this???*

I have several server level triggers, that I want users to deny viewing definition of. I control server level permissions, users aren't sysadmins etc. I've tried 2 options: ### DENY VIEW ANY DEFINITION to public it prevents users from even seeing that those triggers exist (and who knows what else), so I don't want to go with that option ### CREATE SERVER TRIGGER ... WITH ENCRYPTION It's an option, but it says that those triggers won't be replicated, and I need to have those triggers replicated (although I can recreate them on replicas, if necessary). Could you advice if there any other options? Ideally it would be nice to have something like DENY VIEW DEFINITION ON OBJECT::trigger_name TO PUBLIC but I can't make it work for server level trigger

I am trying to understand whether the primary key is encrypted when I choose to [encrypt at rest for AWS DynamoDB](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/encryption.howitworks.html) - 1. If the primary key is encrypted, how does a primary-key lookup get performed under the hood? - 2. If the primary key is not encrypted, then I know how DynamoDB is implemented under the hood, but it seems that it's not entirely safe for some use cases. Any comment on this? Furthermore, if I choose to have a Global Secondary Index for my DynamoDB table, are the primary key of the Global Secondary Index table encrypted? - 1. If not encrypted, I kinda understand how it works under the hood, but certainly some of my data get revealed in plain text; - 2. If encrypted, I'd appreciate understanding how it works.

I successfully encrypted my MariaDB database using file_key_management and following this guide . Now I'd like to change the encryption key and suppose I need to decrypt the database first, before I can apply a new one.

ALTER TABLE mytable ENCRYPTED=NO;

I was able to decrypt all tables using this command. I was also able to decrypt also the system tables using the SET GLOBAL command and the server now starts unencrypted without a key. With a new key, however, I still get an error message saying wrong decryption key. Why does the database still remember the old key and how to set a new one?

SET GLOBAL innodb_encrypt_tables=OFF;
SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION

I'm using MySQL 5.7 and I want to set up SSL to force the client to use a cert. This cert is a self-signed cert that I generate. I have the cert for both server and client being: - CA - cert - key Where the CA has the cert for server and client. I then configured the config file (mysqld.conf) to use the cert: [mysqld] ssl ssl-ca=/database/mysql/ssl/ca-cert.pem ssl-cert=/database/mysql/ssl/master-public.pem ssl-key=/database/mysql/ssl/master-private.pem And run the command to enforce it: GRANT ALL PRIVILEGES ON database TO 'user'@'192.168.10.10' IDENTIFIED BY 'password' REQUIRE SSL; I can see SSL is enabled and it's picking up the right files running the command: SHOW VARIABLES LIKE '%ssl%'; However, from the client machine (192.168.10.10. in this example) I can connect via command line without specifying any cert and it just connects. I can see the connection is using SSL with: mysql> \s SSL: Cipher in use is DHE-RSA-AES256-SHA But the idea is to allow t he connection only if I use the certs. Is there a way to enforce it to make sure it won't connect without specifying the cert ? So I can configure the application to use it and make sure other applications wouldn't access it.

New installation of Debian 10, with MariaDB 10.3.29. Trying to set up encryption-at-rest, but MariaDB won't start due to an error...

[ERROR] /usr/sbin/mysqld: unknown variable 'file-key-management-filename=/etc/openssl/keys/keyfile.key'

My server.cnf has...

[mariadb-10.3]
plugin-load-add = file_key_management
file_key_management_filename = /etc/openssl/keys/keyfile.key
file_key_management_encryption_algorithm = AES_CTR

The above config I have working in a Centos7 system, so I'm baffled as to why it isn't working on Debian10. This message is what I encountered when trying to install encryption in a MariaDB pre-10.1 version that didn't have encryption as an option, but I know encryption is an option after Mariadb 10.1, so it should be available in this package (unexciting installation via apt), right? I have tried moving the cnf code around, to the [mysqld] and [mariadb] sections to no avail. There are no previous versions of MariaDB hanging around - this is a clean install. Has anyone encountered this issue and solved it already?

I have implemented the encryption in SQL Server 2016 using Always Encrypted. I want to know how to rotate the column master key after the initial one year validity using SQL Server Management Studio 17.0. I'm looking for the steps that can executed **using only SSMS** and not PowerShell or any other program. Is it technically possible to rotate the key only using SSMS? Experts advice please.

There are many ways you can see an encrypted procedure. Example of an encrypted procedure in SSMS: There is this very good question: How to view an encrypted view or stored procedure where you can see many ways of viewing the code of your encrypted procedure, either using third party tools , the Dedicated Admin Connection , reversing the RC4 stream cipher just to mention a few, but there are other good answers there. Here is another spectacular way to see the code of a encrypted stored procedure using the DAC and a linked server. How then can I deploy encrypted stored procedures in my client's databases, so they would not be able to decrypt and see the code? In the past, they used our code in their own stored procedures and broke the integrity of the data in many aspects and areas. We don't want that to happen again; if they need any changes or improvements, they can go through support and we can arrange it. They host the databases and have full access/control and responsibility for backups, maintenance, etc. I can't give more details, maybe their DBAs are reading this right now ;) I was thinking maybe I could add or attach the decryption to a password...? I would also consider third party tools. Please mention if you have a positive experience with any such tools, but I would prefer to do it via SQL Server. Question: Is there a way to deploy encrypted stored procedures that cannot be decrypted?

I've experienced a few companies that have various different codebases ranging from 20 year old code without the source anymore to 1 to 10 year old code with the source but thousands of database calls. For certain database fields the companies want to encrypt a column, for example a person's address, without needing to update all the code that calls to that table. They also want the column to still be searchable in where clauses such as "what people are located at 1st street" where the where clause is street like '%1st street%'. Is there a way to easily perform this encryption so that SQL server handles the database encryption and decryption all in the background on the server and not have to change all the code to handle it? If not, what would be the simplest way to accomplish this that would help alleviate massive code changes, unless massive code changes is the only way?

Am facing some below problem in my mongodb replication environment I have 3 replica set environment with SSL enabled and ssl mode as preferSSL 3 servers has 3 different Key file with common CA file when i try to connect the mongo shell with below command it works fine mongo --ssl --host hostname:port --sslPEMKeyFile "/home/mongod/keyfile.pem" --sslCAFile /home/mongod/CAfile.pem My question is how to provide these details in connection string to connect the database from application perspective MongoDB version: 4.0.2 OS: Amazon Linux Thanks, Vignesh

I am using SQL Server 2022 Standard Edition. It has an additional option of setting *Force Strict Encryption*, which I have set on the server side. Using SSMS on a client machine, I am able to connect and access a user table. But I am failing to do the same from a C++ application. Below is the sample code I am trying:

-cpp
auto pDatabase = new CDatabase();
CString connString = L"DRIVER={ODBC Driver 18 for SQL Server};Network=DBMSSOCN;DATABASE=TESTTDE;Encrypt=strict;TrustServerCertificate=no;HostNameInCertificate=10.100.200.300;Mars_Connection=yes;SERVER=10.100.200.300\\TDE,2144;UID=Supervisor;PWD=password;";
auto reply = pDatabase->OpenEx(connString, CDatabase::noOdbcDialog);
CStringArray userNameList;
CString strUserName;
CStringW strUserNameW;
CString SQLString = L"select * from TESTTDE.dbo.UserTable;";
CRecordset userRecords(pDatabase);
userRecords.Open(CRecordset::forwardOnly, SQLString, CRecordset::readOnly);
while (!userRecords.IsEOF())
{
	userRecords.GetFieldValue(L"Name", strUserNameW);
	strUserName = CW2A(strUserNameW.Trim());
	userNameList.Add(strUserName.Trim());
	userRecords.MoveNext();
}
userRecords.Close();

userRecords.Open generates an exception and I am unable to access the database. Can someone shed some light on this? What could I try to make this work? --- I am using a self-signed certificate. It works if I use *Force Encryption* without enabling *Force Strict Encryption* at the server. Exception at client side:

-none
The incoming tabular data stream (TDS) protocol stream is incorrect.
The stream ended unexpectedly.
State:28000,Native:4002,
Origin:[Microsoft][ODBC Driver 18 for SQL Server][SQL Server]

Server log:

-none
The SQL Server or the endpoint is configured to accept only 
strict (TDS 8.0 and above) connections.
The connection has been closed.

Is there anything that prevents self-signed certificate usage with *strict* encryption? --- Some additional observations: Using low-level SQL APIs from a sample application I was able to fetch data from the server even when Force Strict Encryption was enabled. The code is below:

-cpp
SQLHANDLE env;
SQLHANDLE dbc;
SQLHANDLE stmt;
SQLRETURN ret;

SQLWCHAR* connStr = (SQLWCHAR*)L"Driver={ODBC Driver 18 for SQL Server};Server=10.100.200.300\\TDE;Database=TESTTDE;Uid=Supervisor;Pwd=password;Encrypt=strict;";

// Allocate environment handle
ret = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
if (ret == SQL_ERROR) {
	std::wcerr << L"Error allocating environment handle." << std::endl;
	return;
}

// Set the ODBC version environment attribute
ret = SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0);
if (ret == SQL_ERROR) {
	checkDiagnostic(env, SQL_HANDLE_ENV);
	SQLFreeHandle(SQL_HANDLE_ENV, env);
	return;
}

// Allocate connection handle
ret = SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);
if (ret == SQL_ERROR) {
	checkDiagnostic(env, SQL_HANDLE_ENV);
	SQLFreeHandle(SQL_HANDLE_ENV, env);
	return;
}

// Connect to the data source
ret = SQLDriverConnectW(dbc, NULL, connStr, SQL_NTS, NULL, 0, NULL, SQL_DRIVER_COMPLETE);
if (ret == SQL_ERROR) {
	checkDiagnostic(dbc, SQL_HANDLE_DBC);
	SQLFreeHandle(SQL_HANDLE_DBC, dbc);
	SQLFreeHandle(SQL_HANDLE_ENV, env);
	return;
}

// Allocate statement handle
ret = SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
if (ret == SQL_ERROR) {
	checkDiagnostic(dbc, SQL_HANDLE_DBC);
	SQLDisconnect(dbc);
	SQLFreeHandle(SQL_HANDLE_DBC, dbc);
	SQLFreeHandle(SQL_HANDLE_ENV, env);
	return;
}

// Execute a query
SQLWCHAR* query = (SQLWCHAR*)L"SELECT * FROM UserTable";
ret = SQLExecDirectW(stmt, query, SQL_NTS);
if (ret == SQL_ERROR) {
	checkDiagnostic(stmt, SQL_HANDLE_STMT);
}
else
{
	SQLWCHAR name;
	while (SQLFetch(stmt) == SQL_SUCCESS) {
		ret = SQLGetData(stmt, 1, SQL_C_WCHAR, name, sizeof(name), NULL);
		if (SQL_SUCCEEDED(ret)) {
			AfxMessageBox((LPCTSTR)name);
		}
	}
}

// Clean up
SQLFreeHandle(SQL_HANDLE_STMT, stmt);
SQLDisconnect(dbc);
SQLFreeHandle(SQL_HANDLE_DBC, dbc);
SQLFreeHandle(SQL_HANDLE_ENV, env);

I suspect that MFC class API CRecordset::Open() has some issue in supporting TDS 8.0.

As a database point person, I am required to encrypt SSN numbers wherever they are in a table. We use PostgreSQL. My application counterparts require that we use the AES-256-GCM encryption algorithm. However, I am not too sure how to apply that in postgresql. After googling around, I am able to encrypt using either one of these two AES methods: 1\. UPDATE ssnTable SET encrypted_ssn = encrypt(encrypted_ssn::bytea, 'mykey', 'aes')` 2. UPDATE ssnTable SET encrypted_ssn = pgp_sym_encrypt(encrypted_ssn, 'mykey', 'compress-algo=1, cipher-algo=aes256') None of the above specifically uses the 'aes-256-gcm' algorithm, which is what I'm being asked to use. Any idea on how to use this algorithm on the column, in PostgreSQL?

In one of our project, I tried to use PostgreSQL's security extension pgcrypto. I found that is function, PGP_SYM_ENCRYPT is quite handy. Using this function enable me to query encrypted data with LIKE %keyword%, BETWEEN etc, which I can not achieve if I use programming language such as Java or PHP to encrypt the data then insert it into the database. However, I found an issue. Once I execute queries with PGP_SYM_ENCRYPT new line in the log file is written. Interestingly, the log is including the password in PLAIN! Of course, IMO, this is unacceptable. I want to secure my password, in the same time I also want to log any change (INSERT, UPDATE, DELETE) into my database. Is there any way to **hide** this password information from the log file?