I have a named instance of SQL Server 2019 Developer Edition running on my host Windows 10. Using SSMS I can log in locally using Windows Auth or a SQL Server account. Whenever I try this from my Laravel site running on my Linux VM running under Hyper-V (using the sa account), the authentication fails. SQLSTATE: [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Login failed for user 'sa'. This only started failing recently after KB5015807 was installed on my Windows10 host, uninstalling the patch makes the authentication work again. Are there any known issues/changes in this patch for SQL Server auth? UPDATE 1 (7th Sept 2022): So, I've since moved the linux VM onto VMWare ESXi 6.5 (to work around Hyper-V). I'm still getting the same error, though I did find an error item in the logs: Login failed for user 'sa'. Reason: An attempt to login using SQL authentication failed. Server is configured for Integrated authentication only. Source MSSQLSERVER Category (4) Event 18456 Computer [MyHostMachine] Of course, it is configured for mixed mode. select serverproperty('IsIntegratedSecurityOnly') returns 0 and my sa password works when logging in through SSMS. Any ideas? UPDATE 2 (12th Sept 2022): Got my dev machine working under ESXi, Laravel doesn't like port 1433 being specified, also updated the production server (which still works). Updated my old Hyper-V VM, which still doesn't work, so definitely a Windows/Hyper-V issue - hopefully a future patch will resolve it.

I am trying to set up IAM authentication in an AWS RDS MySQL database. I've followed the guidance in AWS' documentation (which I see is the same as on various other webpages about the topic). I know that the problem is not the VPC security group, because the same client system (a Windows desktop) can successfully connect to the same AWS RDS MySQL database using MySQL Workbench. I assume that the problem is not SSL, as the MySQL Workbench is connecting over SSL, and the mysql command line client which I'm trying to use is the binary provided with MySQL Workbench, and the --ssl* options produce reasonable output (detailed below). I don't think that the problem is with the creation and grants of the user. Details below. The most likely place where I've messed up I think is in the IAM Policy, although it doesn't look that complicated, and I've followed the documentation and examples that I've seen, so I don't know what it is that I might have done wrong there. Help getting past surely some kind of my own error blindness will be appreciated, thank you in advance! Various critical configuration and identity parameters: AWS Region: eu-west-2 AWS RDS database type/engine: MySQL (Community, v8 series, specifically v8.0.35) ARN: arn:aws:rds:eu-west-2:MyAcctNumber:db:my-test2-db IAM DB authentication: enabled parameter: require_secure_transport: 1 The IAM Policy which is applied to the user: { "Version": "2012-10-17", "Statement": [ { "Sid": "VisualEditor0", "Effect": "Allow", "Action": "rds-db:connect", "Resource": [ "arn:aws:rds-db:eu-west-2:myclientid:dbuser:db-instancestring/myIAMuser", "arn:aws:rds-db:eu-west-2:myclientid:dbuser:*/*" ] } ] } (n.b. Not that I hope it would matter anyway, although I call my database user "myIAMuser" throughout this post, the database username is in fact in all lower-case). The AWS DB IAM user's GRANTs in the database: #Grants for myIAMuser@% 'GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO myIAMuser@% WITH GRANT OPTION' (I compared this to the GRANTs of the 'admin' user, and while admin has a few extras, none of them seem to be relating to basic permission to connect). Getting an AWS IAM authentication token: TOKEN="$(aws rds generate-db-auth-token --hostname $RDSHOST --port 3306 --region eu-west-2 --username myIAMuser )" echo $TOKEN my-test2-db.cfxxx3bdbuck.eu-west-2.rds.amazonaws.com:3306/?Action=connect&DBUser=myIAMuser&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=CREDENTIAL%2F20240820%2Feu-west-2%2Frds-db%2Faws4_request&X-Amz-Date=20240820T123237Z&X-Amz-Expires=900&X-Amz-SignedHeaders=host&X-Amz-Signature=1234567890abcdef1234567890abcdefabcdef1234567890abcdef (above edited to remove real details) Just to prove that my IAM credentials are good: $ aws rds describe-pending-maintenance-actions --region eu-west-2 PENDINGMAINTENANCEACTIONS arn:aws:rds:eu-west-2:myaccountid:db:somedbname PENDINGMAINTENANCEACTIONDETAILS system-update New Operating System update is available Trying to connect using the mysql CLI: $ echo "RDSHOST" my-test2-db.cfxxx3bdbuck.eu-west-2.rds.amazonaws.com $ mysql --host=$RDSHOST --port=3306 --ssl-mode=required --ssl-ca=./eu-west-2-bundle.pem --enable-cleartext-plugin --user=myIAMuser --password=$TOKEN mysql: [Warning] Using a password on the command line interface can be insecure. WARNING: no verification of server certificate will be done. Use --ssl-mode=VERIFY_CA or VERIFY_IDENTITY. ERROR 1045 (28000): Access denied for user 'myIAMuser'@'dyndsl-012-034-037-172.qqq-ip-backbone.xx' (using password: YES) I have tried increasing the general log level to 3; that did not produce any further information than that this user's access attempt was denied. I added the MARIADB AUDIT option; that, too, provided no further information. Deliberately putting in something that wouldn't be a valid AWS IAM authentication token in the --password option makes no difference. The mysql client provided with MySQL Workbench seems to have been compiled without debugging options: $ mysql --help 2>&1|grep -i debug -#, --debug[=#] This is a non-debug version. Catch this and exit. --debug-check This is a non-debug version. Catch this and exit. -T, --debug-info This is a non-debug version. Catch this and exit. $ mysql --debug=9 --host=$RDSHOST --port=3306 --ssl-mode=required --ssl-ca=./eu-west-2-bundle.pem --enable-cleartext-plugin --user=myIAMuser --password=$TOKEN mysql: [ERROR] C:\Program Files\MySQL\MySQL Workbench 8.0 CE\mysql.exe: Option 'debug=9' was used, but is disabled. .. so I don't seem to be able to ask for more detail on what/why failed that way, either. Please forgive me if I've left out any pertinent detail - just ask. Thanks again in advance for suggestions on debugging this. -Jay

I would like to kill connections for individuals using certain sql auth logins in SSMS that are reserved for our apps. They should have to go out of their way to reauthenticate into sql and at least feel a little pain for doing something they shouldn't be. This is the query similar to one I use to identify spids that should be killed. I would take those spids and iterate through and KILL them in the same fashion I have seen in many other posts. I believe my method actually uses a cursor, but that is neither here nor there. SELECT sp.spid FROM sys.sysprocesses sp JOIN sys.server_principals sl ON sl.sid = sp.sid WHERE sl.type='S' AND sp.Program_Name LIKE 'Microsoft SQL Server Management Studio%' and sp.loginame NOT IN ('pilon','validlogin1','validlogin2') I have tried to kill spids that fit this criteria and then all they have to do is run a query and it reconnects. I found this MS Documentation on the ALTER LOGIN command, that would lead me to believe that is the expected behavior, but I have found the opposite. Is there a different way to go about this than 'KILL 1234'? Is there an additional 'FORCE' command or something that I am not aware of? I have considered using the Resource Governor, but don't want to incur the overhead necessary to make that work. I have no idea if this is even possible. Any suggestions are welcome. We have considered a shame email as well, but that is a little more in your face than I am after.

Apologies for the length of this but I'm app developer, not a SQL DBA nor network Admin, so I'm out of my area and just wanted to be sure I documented everything as well as I could. This is a problem about authenticating from a client app using Kerberos authentication and a Windows Domain Identity, to a SQL 2022 instance on a host that hosts both SQL 2019 and now SQL 2022. I’ve inherited a problem that was abandoned more than a year ago and got it simply because it involves SQL client connections from an application, or that I’m the old man in group which really doesn’t mean I have more experience, at least not like it used to, but they think it does, so I let them believe it. I’ve solved a number problems that were missed, but I’m still short of making it work because I’m having trouble getting the SPN’s and network protocols correct for Kerberos client connections. Each instances of SQL Server has a unique instance name, the 1st has the instance name “Sql2019”, the 2nd instance “Sql2022” and it’s the 2nd instance that needs Kerberos authentication. One of the things missed by the original group were the client libraries used for OLE DB connections; they were still using SQL Native Client. I have since installed the newer MSOLEDBSQL libraries into the client machines and made the code changes to use Ole DB SQL Connections and other client side components for Kerberos client connections, following MS Documentation on this topic. And not knowing if I was going at this the right way, I installed these client libraries on the machine hosting SQL 2019 and SQL 2022. I believe I have all of the correct elements set up on the client side, I’m not sure about the server side, because I can make an OLE DB connection from the client app and access the application databases, but when a connection is made that requires access to the restricted elements of SQL 2022, such as the Master DB or SSIS when installing and configuring DTS Packages, client authentication fails. The error message I’m getting right now is a SQL Connection timeout SQL Error 87. If I understand MS Docs correctly, the reason for this is because the original SPN setup is no longer valid and or, when an OLE DB client connection initially fails Kerberos authentication, it falls back to NTLM. But SQL 2022 can’t authenticate the NTLM connection because something is incomplete or missing with the SPN’s or something is incorrectly configured with regards to the network protocols used, Named Pipes vs TCP/IP. I’m stuck in this latter area and re-reading the MS Docs is not giving me any insights on where to go next. Here is where things stand as of this post: 1. The SQL Server name is “**SqlServer**” 2. The instance names are “**Sql2019**” and “**Sql2022**”. 3. The host is joined to the domain. 4. The TCP/IP ports for both instances use the default port numbers. 5. The Named Pipes protocol use the default SQL Names assigned at installation. 6. The account that both SQL instances runs under was changed before I was given this task, from NT Service to a domain account named (**SysCred**) and it’s used exclusively for services. That account is a member of the domain wide *Administrators Group*, and I have confirmed that on SQL Server, it is also a member of the *local Administrators Group*. I’m not sure if this is relevant or not, but I have left it as it is. 7. The same can be said for the application that they want accessing SQL 2022 with Kerberos authentication, it too runs under a domain account (**CoreSrvcs**), and at present, that domain account is a member of the *local SQL Server’s Administrators Group*, so I have left it as it is. 8. On SQL Server 2022 I found the following: 9. A SQL Login for the domain account (**SysCred**), who is a member of (*sysadmin, serverdmin*) 10. A SQL 2022 Login for the domain account (**CoreSrvcs**), who is a member of (*sysadmin, serverdmin*) 11. This login is mapped to the application databases, the *Master DB*, and to the *SSIS database*. 12. The SQL Host has AD LDS service installed to synch with the domain controller, and that services appears to be working correctly, I see no error messages in the event logs for it. 13. I queried for SPN’s on the SQL Server and found the following: 14. For the domain service account (**SysCred**) that SQL instances run under 15. *MSSQLSvc/SqlServer.domainame:50895* 16. *MSSQLSvc/SqlServer.domainame:SQL2022* 17. *MSSQLSvc/SqlServer.domainame:1433* 18. *MSSQLSvc/SqlServer.domainame:SQL2019* 19. For the domain account (**CoreSrvcs**) that applications run under: 20. *MSSQLSvc/SqlServer.domainame:50895* 21. *MSSQLSvc/SqlServer.domainame:SQL2022* 22. *MSSQLSvc/SqlServer.domainame:1433* 23. *MSSQLSvc/SqlServer.domainame:SQL2019* 24. In the application components that need to query the *Master DB* or the *SSISSDB*, I changed the code from using the elements in the *System.Data.SqlClient* namespace or the *Microsoft.Data.SqlClient* namespace, to use elements from *System.Data.Ole* as per Microsoft Docs, and as long as I only query the application database, everything works. 25. But when the application attempts to access the *Master DB*, or install and configure DTS packages against the *SSISDB* database, the client connection times out and I see this SQL server error 87. 26. NOTE one of the last changes made per MS Docs was to use a SPN in place of the SQL Server Instance name when setting up an Ole DB Connection, on my last few tries I was using one of the SPN’s listed above, but all produced the same error. My last thought before asking for help was to change the protocol order of SQL Server Configuration Manager, from having TCP/IP first to Named Pipes 1st, not sure this would make a difference, and while I wait for a reply I’ll checkout some trouble shooting tips and tools from https://github.com/microsoft/CSS_SQL_Networking_Tools/wiki

Maybe my understanding was not clear about user authentication in MSSQL. I am very new to DataBase subjects. Thought of checking with the experts here. I am planning to provide access to SQL users to connect only via JDBC from the backend code. and the user should not connect via SSMS or other tools. please share how that can achieve or possible or not

In my different web applications I use both ways, however, I would like to learn which method is better: 1: server=localhost;database=x; integrated security=SSPI;persist security info=False; Trusted_Connection=Yes; 2: server=localhost;database=x;uid=sa;pwd=y; In my web application, for each database query, I open a connection and then close the connection. So I am pretty much using the regular style such as: using (SqlConnection connection = new SqlConnection(srConnectionString)) { connection.Open(); using (SqlDataAdapter DA = new SqlDataAdapter(strQuery, connection)) { DA.Fill(dSet); } } You can safely assume that there are hundreds or even thousands of queries at any given second. My questions: Are there any performance wise difference between 2 authentication methodology? Are there any security difference between 2 authentication methodology? I don't allow remote connections to the SQL Server. So only local connections are allowed. Operating system Windows Server 2019

Seems like I am using an older version on Mysql Server which uses sha_256 plugin for Authentication (which is now deprecated) .Because of this, I am unable to connect to my server with third party softwares like beekeper studio. But I am able to connect from the terminal itself. So I went through the [Mysql manual](https://dev.mysql.com/doc/refman/8.0/en/sha256-pluggable-authentication.html) . It shows how to change the default authentication plugin , but before changing it, I just want to know what is my default authentication plugin. How to do that?

I'm trying to grant access to my Azure SQL database (on an elastic pool) to an external developer using SQL Authentication. In the database (not master) I ran the following commands: create user [UserName] with password='Password', default_schema=[dbo] alter role db_owner add member [UserName] He can't access the database, he gets the error: `Login failed for user 'UserName'. (.Net SqlClient Data Provider) Error Number: 18456 Severity: 14 State: 1 Line Number: 65536` Both our IPs have been whitelisted, but only I can login to the database with those credentials. Any idea where things are going wrong?

I come up with this. A Domain admin windows user is able to login to a SQL Server 2017 instance from any client computer, but it can not login from the console of the server. TCP/IP and Shared memory protocols are enabled and the "Domain admins" group is added as well as a sysadmin. In order to login from the console I ended up adding the specific domain admin user to workaround the problem at the console but why the same windows user can login from outside the server but it can not do that at the console through AD groups?

SQL Server supports creating logins using SQL, Windows, certificates and asymmetric keys . Is it possible to remove the option of creating logins with Windows authentication (and just allow SQL authentication for example)?

I am currently in the process of trying to connect to my local postgres 12 db. In the past (9.3/9.6) you were able to edit the .pg_hba file to modify which connections were allowed but that file doesn't no exist in 12 (it has been over a year since I have used postgres). I am unsure what the equivalent is or if there is a different method that should be used to connect to the db. I am struggling to find any tutorials for how to accomplish this. Every one that I have seen so far just assumes that you already have something ready to connect and simply provides the necessary command needed to connect. I do have an ssh key on my local computer which I assume should be sufficient to use to connect. dbeaver requires the following: host/ip - localhost post - defaults to 22 user name - postgres (default) authentication method - Public Key Private Key - path to my private key in .ssh What here am I missing to connect to my postgres db? Side note - I am unable to connect using ident either since dbeaver always requests a password even if there isn't one

We have following config of Availability Group: server1.domain.com - primary server2.domain.com - sync secondary server3.domain.com - async secondary sql-listener.domain.com - availability group listener DNS name Recently we have setup Read Only Routing (ROR) Read Only Routing URLs: server1 - TCP://server1.domain.com:1433 server2 - TCP://server2.domain.com:1433 server3 - TCP://server3.domain.com:1433 Read Only Routing Lists: server1: server2, server3, server1 server2: server1, server3, server2 server3: server2, server1, server3 It worked fine when tested in SSMS using Windows Authentication Login (Domain\MyLogin) with Options >> Additional Connection Parameters tab -> ApplicationIntent=ReadOnly While server1 is primary, I connect in SSMS to AG Listener (sql-listener.domain.com) using above parameter and run select @@servername, and it shows server 2, which means ROR works Issues/Questions: When I try to connect in SSMS to AG Listener using SQL Authentication Login (SQLAuthLogin), and ApplicationIntent=ReadOnly parameter, and then run select @@servername, it shows me server1 `>>> So in my case, ROR does not work with SQL Authentication Login is there any way to fix this ?` add: double checked SQLAuthLogin's SID on server1 and server2 - SID is the same, SQLAuthLogin can login to both servers and query MyDatabase just fine