By using AWS re:Post, you agree to the Terms of Use

Questions tagged with Microsoft SQL Server

Sort by most recent

Browse through the questions and answers listed below or filter and sort to narrow down your results.

Not able to connect to MS-SQL Database by using SQL Server Management Studio installed on my local laptop

Hi, We have just created a brand new account in Amazon EC2. We have also created a default MS-SQL Server database along with a new EC2 machine. However, when we try to connect to this database via SQL Server Management Studio installed on my local laptop, we are running into errors. These errors are not unique. These are typical errors which come while trying to configure connectivity between Management studio and remote database. We have followed all articles and other information available both at Microsoft Technet as well as AWS, however the error have remained unchanged. Details of the error are given below. I would greatly appreciate it if someone have a look and answer them ASAP. Many many Thanks, Manoj Kumar Error Message 1 - Cannot connect to vibexchange.c3uqqhmm412p.ap-south-1.rds.amazonaws.com. Details - Cannot connect to vibexchange.c3uqqhmm412p.ap-south-1.rds.amazonaws.com. Error Message 2 - 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) (Framework Microsoft SqlClient Data Provider) ------------------------------ For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-53-database-engine-error ------------------------------ Error Number: 53 Severity: 20 State: 0 ------------------------------ Program Location: at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at Microsoft.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean withFailover, Boolean isFirstTransparentAttempt, SqlAuthenticationMethod authType, String certificate, ServerCertificateValidationCallback serverCallback, ClientCertificateRetrievalCallback clientCallback, Boolean useOriginalAddressInfo, Boolean disableTnir) at Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover, Boolean isFirstTransparentAttempt, Boolean disableTnir) at Microsoft.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout) at Microsoft.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance) at Microsoft.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, ServerCertificateValidationCallback serverCallback, ClientCertificateRetrievalCallback clientCallback, DbConnectionPool pool, String accessToken, SqlClientOriginalNetworkAddressInfo originalNetworkAddressInfo, Boolean applyTransientFaultHandling) at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) at Microsoft.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions) at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) at Microsoft.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) at Microsoft.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry) at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry, SqlConnectionOverrides overrides) at Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides overrides) at Microsoft.Data.SqlClient.SqlConnection.Open() at Microsoft.SqlServer.Management.SqlStudio.Explorer.ObjectExplorerService.ValidateConnection(UIConnectionInfo ci, IServerType server) at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser() Details - 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) (Framework Microsoft SqlClient Data Provider) For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-53-database-engine-error Error Number: 53 Severity: 20 State: 0 at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at Microsoft.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean withFailover, Boolean isFirstTransparentAttempt, SqlAuthenticationMethod authType, String certificate, ServerCertificateValidationCallback serverCallback, ClientCertificateRetrievalCallback clientCallback, Boolean useOriginalAddressInfo, Boolean disableTnir) at Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover, Boolean isFirstTransparentAttempt, Boolean disableTnir) at Microsoft.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout) at Microsoft.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance) at Microsoft.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, ServerCertificateValidationCallback serverCallback, ClientCertificateRetrievalCallback clientCallback, DbConnectionPool pool, String accessToken, SqlClientOriginalNetworkAddressInfo originalNetworkAddressInfo, Boolean applyTransientFaultHandling) at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) at Microsoft.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions) at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) at Microsoft.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) at Microsoft.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry) at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry, SqlConnectionOverrides overrides) at Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides overrides) at Microsoft.Data.SqlClient.SqlConnection.Open() at Microsoft.SqlServer.Management.SqlStudio.Explorer.ObjectExplorerService.ValidateConnection(UIConnectionInfo ci, IServerType server) at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser() Error 3 - The network path was not found Details - The network path was not found
1
answers
0
votes
18
views
asked 6 days ago

How to decrypt RDS SQL Server backup done with msdb.dbo.rds_backup_database

Hello, we have an RDS with SQL Server and we do backups to S3 with procedure msdb.dbo.rds_backup_database provided by AWS as explained at https://docs.aws.amazon.com/es_es/AmazonRDS/latest/UserGuide/SQLServer.Procedural.Importing.html#SQLServer.Procedural.Importing.Native.Using.Backup. We are encrypting theses backups with KMS through msdb.dbo.rds_backup_database parameter. Then we download the backup file and we try to decrypt with aws-encryption-cli --decrypt but we get the error "Error: 51 is not a valid SerializationVersion" .This is the error with verbose output: ``` 2022-09-21 15:19:11,156 - MainThread - aws_encryption_sdk.internal.formatting.deserialize - DEBUG - Starting header deserialization 2022-09-21 15:19:11,156 - MainThread - aws_encryption_sdk.streaming_client - DEBUG - Closing stream --- Logging error --- Traceback (most recent call last): File "C:\Users\Administrator\AppData\Local\Programs\Python\Python310\lib\site-packages\aws_encryption_sdk\internal\formatting\deserialize.py", line 97, in _verified_version_from_id return SerializationVersion(version_id) File "C:\Users\Administrator\AppData\Local\Programs\Python\Python310\lib\enum.py", line 385, in __call__ return cls.__new__(cls, value) File "C:\Users\Administrator\AppData\Local\Programs\Python\Python310\lib\enum.py", line 710, in __new__ raise ve_exc ValueError: 51 is not a valid SerializationVersion During handling of the above exception, another exception occurred: Traceback (most recent call last): File "C:\Users\Administrator\AppData\Local\Programs\Python\Python310\lib\site-packages\aws_encryption_sdk_cli\internal\io_handling.py", line 240, in _single_io_write header=json_ready_header(handler.header), File "C:\Users\Administrator\AppData\Local\Programs\Python\Python310\lib\site-packages\aws_encryption_sdk\streaming_client.py", line 218, in header self._prep_message() File "C:\Users\Administrator\AppData\Local\Programs\Python\Python310\lib\site-packages\aws_encryption_sdk\streaming_client.py", line 792, in _prep_message self._header, self.header_auth = self._read_header() File "C:\Users\Administrator\AppData\Local\Programs\Python\Python310\lib\site-packages\aws_encryption_sdk\streaming_client.py", line 807, in _read_header header, raw_header = deserialize_header(self.source_stream, self.config.max_encrypted_data_keys) File "C:\Users\Administrator\AppData\Local\Programs\Python\Python310\lib\site-packages\aws_encryption_sdk\internal\formatting\deserialize.py", line 346, in deserialize_header version = _verified_version_from_id(version_id) File "C:\Users\Administrator\AppData\Local\Programs\Python\Python310\lib\site-packages\aws_encryption_sdk\internal\formatting\deserialize.py", line 99, in _verified_version_from_id raise NotSupportedError("Unsupported version {}".format(version_id), error) aws_encryption_sdk.exceptions.NotSupportedError: ('Unsupported version 51', ValueError('51 is not a valid SerializationVersion')) During handling of the above exception, another exception occurred: Traceback (most recent call last): File "C:\Users\Administrator\AppData\Local\Programs\Python\Python310\lib\site-packages\aws_encryption_sdk\streaming_client.py", line 228, in __exit__ self.close() File "C:\Users\Administrator\AppData\Local\Programs\Python\Python310\lib\site-packages\aws_encryption_sdk\streaming_client.py", line 995, in close raise SerializationError("Footer not read") aws_encryption_sdk.exceptions.SerializationError: Footer not read During handling of the above exception, another exception occurred: Traceback (most recent call last): File "C:\Users\Administrator\AppData\Local\Programs\Python\Python310\lib\logging\__init__.py", line 1100, in emit msg = self.format(record) File "C:\Users\Administrator\AppData\Local\Programs\Python\Python310\lib\logging\__init__.py", line 943, in format return fmt.format(record) File "C:\Users\Administrator\AppData\Local\Programs\Python\Python310\lib\site-packages\aws_encryption_sdk_cli\internal\logging_utils.py", line 137, in format _record = self.__redact_record(record) File "C:\Users\Administrator\AppData\Local\Programs\Python\Python310\lib\site-packages\aws_encryption_sdk_cli\internal\logging_utils.py", line 123, in __redact_record _record = copy.deepcopy(record) File "C:\Users\Administrator\AppData\Local\Programs\Python\Python310\lib\copy.py", line 172, in deepcopy y = _reconstruct(x, memo, *rv) File "C:\Users\Administrator\AppData\Local\Programs\Python\Python310\lib\copy.py", line 271, in _reconstruct state = deepcopy(state, memo) File "C:\Users\Administrator\AppData\Local\Programs\Python\Python310\lib\copy.py", line 146, in deepcopy y = copier(x, memo) File "C:\Users\Administrator\AppData\Local\Programs\Python\Python310\lib\copy.py", line 231, in _deepcopy_dict y[deepcopy(key, memo)] = deepcopy(value, memo) File "C:\Users\Administrator\AppData\Local\Programs\Python\Python310\lib\copy.py", line 146, in deepcopy y = copier(x, memo) File "C:\Users\Administrator\AppData\Local\Programs\Python\Python310\lib\copy.py", line 211, in _deepcopy_tuple y = [deepcopy(a, memo) for a in x] File "C:\Users\Administrator\AppData\Local\Programs\Python\Python310\lib\copy.py", line 211, in <listcomp> y = [deepcopy(a, memo) for a in x] File "C:\Users\Administrator\AppData\Local\Programs\Python\Python310\lib\copy.py", line 161, in deepcopy rv = reductor(4) TypeError: cannot pickle 'traceback' object Call stack: File "C:\Users\Administrator\AppData\Local\Programs\Python\Python310\lib\runpy.py", line 196, in _run_module_as_main return _run_code(code, main_globals, None, File "C:\Users\Administrator\AppData\Local\Programs\Python\Python310\lib\runpy.py", line 86, in _run_code exec(code, run_globals) File "C:\Users\Administrator\AppData\Local\Programs\Python\Python310\Scripts\aws-encryption-cli.exe\__main__.py", line 7, in <module> sys.exit(cli()) File "C:\Users\Administrator\AppData\Local\Programs\Python\Python310\lib\site-packages\aws_encryption_sdk_cli\__init__.py", line 288, in cli process_cli_request(stream_args, args) File "C:\Users\Administrator\AppData\Local\Programs\Python\Python310\lib\site-packages\aws_encryption_sdk_cli\__init__.py", line 225, in process_cli_request handler.process_single_file(stream_args=stream_args, source=_source, destination=_destination) File "C:\Users\Administrator\AppData\Local\Programs\Python\Python310\lib\site-packages\aws_encryption_sdk_cli\internal\io_handling.py", line 370, in process_single_file operation_result = self.process_single_operation( File "C:\Users\Administrator\AppData\Local\Programs\Python\Python310\lib\site-packages\aws_encryption_sdk_cli\internal\io_handling.py", line 302, in process_single_operation return self._single_io_write( File "C:\Users\Administrator\AppData\Local\Programs\Python\Python310\lib\site-packages\aws_encryption_sdk_cli\internal\io_handling.py", line 235, in _single_io_write with self.client.stream(source=_source, **stream_args) as handler, self.metadata_writer as metadata: File "C:\Users\Administrator\AppData\Local\Programs\Python\Python310\lib\site-packages\aws_encryption_sdk\streaming_client.py", line 232, in __exit__ _LOGGER.exception("Error on closing") Message: 'Error on closing' Arguments: () 2022-09-21 15:19:11,172 - MainThread - aws_encryption_sdk_cli - WARNING - Operation failed: deleting output file: oauth2_pc_backup_2022_09_16_010000_decrypt.bak 2022-09-21 15:19:11,172 - MainThread - aws_encryption_sdk_cli - DEBUG - Encountered unexpected error: increase verbosity to see details. NotSupportedError("Unsupported version 51", "51 is not a valid SerializationVersion") 2022-09-21 15:19:11,172 - MainThread - aws_encryption_sdk_cli - DEBUG - Traceback (most recent call last): File "C:\Users\Administrator\AppData\Local\Programs\Python\Python310\lib\site-packages\aws_encryption_sdk\internal\formatting\deserialize.py", line 97, in _verified_version_from_id return SerializationVersion(version_id) File "C:\Users\Administrator\AppData\Local\Programs\Python\Python310\lib\enum.py", line 385, in __call__ return cls.__new__(cls, value) File "C:\Users\Administrator\AppData\Local\Programs\Python\Python310\lib\enum.py", line 710, in __new__ raise ve_exc ValueError: 51 is not a valid SerializationVersion During handling of the above exception, another exception occurred: Traceback (most recent call last): File "C:\Users\Administrator\AppData\Local\Programs\Python\Python310\lib\site-packages\aws_encryption_sdk_cli\__init__.py", line 288, in cli process_cli_request(stream_args, args) File "C:\Users\Administrator\AppData\Local\Programs\Python\Python310\lib\site-packages\aws_encryption_sdk_cli\__init__.py", line 225, in process_cli_request handler.process_single_file(stream_args=stream_args, source=_source, destination=_destination) File "C:\Users\Administrator\AppData\Local\Programs\Python\Python310\lib\site-packages\aws_encryption_sdk_cli\internal\io_handling.py", line 370, in process_single_file operation_result = self.process_single_operation( File "C:\Users\Administrator\AppData\Local\Programs\Python\Python310\lib\site-packages\aws_encryption_sdk_cli\internal\io_handling.py", line 302, in process_single_operation return self._single_io_write( File "C:\Users\Administrator\AppData\Local\Programs\Python\Python310\lib\site-packages\aws_encryption_sdk_cli\internal\io_handling.py", line 240, in _single_io_write header=json_ready_header(handler.header), File "C:\Users\Administrator\AppData\Local\Programs\Python\Python310\lib\site-packages\aws_encryption_sdk\streaming_client.py", line 218, in header self._prep_message() File "C:\Users\Administrator\AppData\Local\Programs\Python\Python310\lib\site-packages\aws_encryption_sdk\streaming_client.py", line 792, in _prep_message self._header, self.header_auth = self._read_header() File "C:\Users\Administrator\AppData\Local\Programs\Python\Python310\lib\site-packages\aws_encryption_sdk\streaming_client.py", line 807, in _read_header header, raw_header = deserialize_header(self.source_stream, self.config.max_encrypted_data_keys) File "C:\Users\Administrator\AppData\Local\Programs\Python\Python310\lib\site-packages\aws_encryption_sdk\internal\formatting\deserialize.py", line 346, in deserialize_header version = _verified_version_from_id(version_id) File "C:\Users\Administrator\AppData\Local\Programs\Python\Python310\lib\site-packages\aws_encryption_sdk\internal\formatting\deserialize.py", line 99, in _verified_version_from_id raise NotSupportedError("Unsupported version {}".format(version_id), error) aws_encryption_sdk.exceptions.NotSupportedError: ('Unsupported version 51', ValueError('51 is not a valid SerializationVersion')) 2022-09-21 15:19:11,203 - MainThread - aws_encryption_sdk.streaming_client - DEBUG - Closing stream Encountered unexpected error: increase verbosity to see details. NotSupportedError("Unsupported version 51", "51 is not a valid SerializationVersion") ``` We are using aws-encryption-cli --decrypt with the last version for other files and it's working fine. How can we decrypt the backups done with msdb.dbo.rds_backup_database? Best regards.
0
answers
0
votes
16
views
asked 13 days ago

How to rename a database (Not the instance) in mssql

I'm just trying to rename a created database on a 2019 MS SQL running on RDS. I have a database named `mydb` that for multi-tenant purposes, I need to rename to `mydb2` as a workaround for hardcoded values in the app. However, in SSMS, when i try to rename the database, I get this error: ``` TITLE: Microsoft SQL Server Management Studio ------------------------------ Unable to rename mydb2. (ObjectExplorer) ------------------------------ ADDITIONAL INFORMATION: Rename failed for Database 'mydb'. (Microsoft.SqlServer.Smo) For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=16.100.47021.0&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Rename+Database&LinkId=20476 ------------------------------ An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) ------------------------------ User does not have permission to alter database 'mydb', the database does not exist, or the database is not in a state that allows access checks. (Microsoft SQL Server, Error: 5011) For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-5011-database-engine-error ------------------------------ BUTTONS: OK ------------------------------ ``` An using for forceful ways like changing the db to single-access, then altering the db doesn't work either because the error says my `admin` user doesn't have permissions to change that on a table. Is the background user `rdsadmin` logged in and preventing me from changing this? Could I restart the service and close any zombie connections? Maybe I make a copy of the database and give it another name? These seems like overkill but I'm not sure what permissions on RDS are preventing me.
1
answers
0
votes
18
views
asked 15 days ago