35

"It depends". If the client vanishes due to network connection loss the query will generally run until it's retrieved enough rows to fill its network send buffer, then stop and get stuck until the TCP connection drops, at which point it'll abort. If it completes before it fills the TCP send buffer it'll complete successfully, so if it's autocommit the query ...


32

The maximum number of connections across SQL Server versions and editions is 32,767. You can determine how many connections SQL Server currently has by looking at: SELECT ConnectionStatus = CASE WHEN dec.most_recent_sql_handle = 0x0 THEN 'Unused' ELSE 'Used' END , CASE WHEN des.status = 'Sleeping' THEN 'sleeping' ...


30

Here is my answer to very similar question on StackOverflow. Depending on your version of postgresql you might run into a bug, that makes pg_stat_activity to omit active connections from dropped users. These connections are also not shown inside pgAdminIII. If you are doing automatic testing (in which you also create users) this might be a probable ...


17

We found that with contained databases / contained users you must specify: GRANT CONNECT TO [YOUR_USER] Otherwise CONNECT seems to be revoked by default. Once we made the above change, we could access the database.


14

You can look at the currently running processes in SSMS in Activity Monitor. You can also get it using sys.dm_exec_sessions as well as sys.dm_exec_connections and sys.dm_exec_requests. Query: SELECT DB_NAME(database_id) as [DB] , login_name , nt_domain , nt_user_name , status , host_name , program_name , COUNT(*) AS [...


14

I think there is no reliable solution for your problem since Application Name is modifiable parameter that cam be changed by any user. Here is how to change it within SSMS: In Connect to Database Object dialog choose Options, open Additional Connection Parameters and choose any name for Application Name like this: Now sys.dm_exec_sessions DMV and ...


12

Check the folowing First check if another instance is runnig in the same machine ps ax | grep mysql Then, check if your file my.cnf (probably in /etc/mysql/ folder) is correctly configured with [mysqld] datadir = /var/lib/mysql/ socket = /var/lib/mysql/mysql.sock Next, you have changed the default data directory, did you gave the right permissions to ...


12

I've listed some alternatives for connection management below, in order of most to least recommended. Increase the connections allowed on the server The total incoming connection limit on the server is determined by the lesser of the limits imposed by the operating system or maxIncomingConnections (aka maxConns in MongoDB 2.4 and earlier). Typically Linux ...


12

I've run into strange behavior with connection pooling in the past, and your scenario aligns well with one of those situations. If your application is using connection pooling (and that's still speculation, at this point, until you confirm or deny that) then you're going to have many connections that remain open. This is by design. Connection pooling aims ...


12

You can use a server logon trigger to make custom logon validations and reject them whenever you see fit. You will see this trigger listed below "Server Objects" and inside "Triggers" if you are using SSMS. For example: CREATE TRIGGER strRejectSSMSConnectionForSQLLogin1 ON ALL SERVER FOR LOGON AS BEGIN IF ORIGINAL_LOGIN() = N'SQLLogin1' AND ...


11

From TechNet sys.dm_exec_connections DMV: the encrypt_option will display FALSE if the connection is not encrypted. Here is an example TSQL statement that can be run by administrators (VIEW SERVER STATE is the required privilege): SELECT session_id, connect_time, net_transport, encrypt_option, auth_scheme, client_net_address FROM sys.dm_exec_connections


11

Local temporary objects are separated by Session. If you have two queries running concurrently, then they are clearly two completely separate sessions and you have nothing to worry about. The Login doesn't matter. And if you are using Connection Pooling that also won't matter. Local temporary objects (Tables most often, but also Stored Procedures) are safe ...


10

Do databases support multiple transactions simultaneously on a single database connection from client? For SQL Server, no. If not, why not? (as multiplexing would save on resource overhead per connection) It would seriously complicate the network protocol, which has to be implemented on multiple client platforms, creating a possible source of bugs and ...


8

It's not a bug. This behaviour is expected. If you need to increase the connections, just change the processes limit: SQL> show parameter processes processes integer 150 SQL> alter system set processes = <integer> scope=[...]; SQL> shutdown immediate; SQL> startup; -- Either using pfile or spfile, you'll ...


8

Traffic will be encrypted regardless of the client encryption specification when you turn on the Force Encryption option on the server side (SSCM-->SQL Server Network Configuration-->Protocols for MSSQLSERVER-->Properties-->Flags-->Force Encryption). If you don't install a certificate from a trusted CA, SQL Server will use a self-signed certificate. Clients ...


8

Using the EXECUTE AS clause of the CREATE {module_type} statement is impersonation, but only at the Database level. The owner is "dbo" (in this case, at least), but that is just a User; a Database-level principal. The KILL command requires an Instance-level permission. By default, when using Database-level impersonation (EXECUTE AS clause or EXECUTE AS USER ...


7

I have found my answer from dbeaver support forum. http://dbeaver.jkiss.org/forum/viewtopic.php?f=3&t=624&p=4261#p4261 First enable TCP/IP for your SQL instance in the SQL Server Configuration Manager tool. Open SSCM and navigate to SQL Server Network Configuration > Protocols for SQLEXPRESS > TCP/IP Enable the TCP/IP protocol in the Properties ...


6

There is no perfmon counter that would you show you this information. Why don't you just pull this directly from SQL Server (at least as how SQL Server sees it)? select session_id, program_name from sys.dm_exec_sessions where is_user_process = 1; If you're looking for data collection, you can periodically run this and store the output of the above ...


6

One minor drawback would be that you would need to add a Subject Alternative Name (SAN) to the certificate that is bound to SQL Server if you are using encrypted connections. This could be a significant pain if you have a "shared" server that hosts a number of smaller applications and you want to set up an alias for each application. If you are using a ...


6

In the ideal sense, this is a process / policy / management issue. Even if someone knows the password, if it is against company policy for anyone but a DBA to connect to Production (well, you might have a Release Engineering team and/or sys admins, etc), and there are penalties for breaking the rules, then that should be sufficient (assuming that such rules ...


6

I don't believe this configuration setting is sent to the server. It controls the client's interpretation of a DNS lookup returning multiple IP addresses, and there is no functional need for the server to be informed about it. And the client doesn't send arbitrary name/value pairs to the server. The fields that a client sends to the server are dictated by ...


5

My issue was that multiple mysql processes were running concurrently. First kill the processes with: sudo pkill mysql and sudo pkill mysqld restart mysql sudo service mysql restart now you should be able to log in mysql -u root -p


5

It would appear that the socket file is missing. service mysql stop and service mysql restart depend on the socket file's existence to shutdown. You will have to connect to mysql using mysqladmin and TCP/IP to shutdown MYSQL_USER=root MYSQL_PASS=rootpassword MYSQL_CONN="-h127.0.0.1 -u${MYSQL_USER} -p${MYSQL_PASS} --protocol=tcp -P3306" mysqladmin ${...


5

The usual solution is to authenticate the user within the web app, then issue a SET ROLE or SET SESSION AUTHORIZATION to "become" the user on a JDBC session that's already authenticated with the database using a fixed username. In both cases the DISCARD ALL command that should be run by any connection pool when returning connections to the pool will ...


5

Your host is wrong, to use the socket it must be "localhost", not the socket address. The url for JDBC to use a socket for connection is: jdbc:mysql://localhost:3307/my database name?socket=/home/aspect_mkn8rd/tmp/mysql.sock (leaving out here the definition of a user and password, as your software seems to require it apart, it would be &user=my user&...


5

sp_reset_connection is an artifact of using Connection Pooling. And what you think you are seeing is not what is actually happening. Connection Pooling is a feature of the client library more than of SQL Server. The client provides most of the functionality, while SQL Server itself mainly provides sp_reset_connection. When using Connection Pooling, the ...


5

When switching our API to connect to an Azure database via a new contained user, we had to change our connection string to include: Persist Security Info=True; While I don't understand why this change was required, I wanted to post here in case it helps someone else out in the future. We originally came to try this from this question.


5

Well, there are four main aspects to this: Why are you asking the general Interwebs for an opinion when there are three other sources that should be checked first, one of which will be definitive and not an opinion: When someone tells you a technical / scientific piece of information, whenever possible, ask them how they know that to be the case. Did they ...


5

30 system processes is absolutely not unheard of, and actually sounds better than normal. However, you should not be using sysprocesses, which is deprecated and only exists for backward compatibility reasons - see the important note right at the top of the doc. (If you're using it solely for the database column, see this recent question, and the comments.) ...


5

Looks like I was just asking the question wrong, I found the answer under: How to get the client IP address from SQL Server 2008 itself?: As Aaron mentioned in the comments, I had to use sys.dm_exec_connections to get the source IP. Here is the final version of the query I used: SELECT s.session_id, s.login_name, DB_NAME(s.database_id) as database_name, ...


Only top voted, non community-wiki answers of a minimum length are eligible