17
votes
How to deny access to SQL Server to certain login over SSMS, but allow over .Net SqlClient Data Provider
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 ...
- 11.1k
13
votes
Accepted
SQL server temp table using # - is it only accessible by the containing query for multiple connections with the same login?
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 ...
- 69.4k
13
votes
Accepted
Logon failed for login due to trigger execution
According to the screenshots you posted you are encountering an Error 17892 which translate roughly to a logon trigger error.
You might have previously had a logon trigger that evaluated if you were ...
- 18.1k
13
votes
Accepted
How to deny access to SQL Server to certain login over SSMS, but allow over .Net SqlClient Data Provider
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 ...
- 3,306
12
votes
Database Connection vs Raw TCP Connection
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 ...
- 47.3k
9
votes
Accepted
Keep H2 in-memory database between connections
Set DB_CLOSE_DELAY=-1
By default, an in-memory database in H2 is discarded after the connection closes.
To override this default behavior, set DB_CLOSE_DELAY to a negative one. You can do so either:
...
- 10.9k
8
votes
sys.sp_reset_connection. Why theres a lot of them ?
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 ...
- 69.4k
8
votes
Accepted
SQL Server SSL Encryption - SelfSign Cert working - why?
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 ...
- 28.3k
8
votes
Stored Procedure Execute as Owner Close Database Connections Not Working
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-...
- 69.4k
7
votes
Azure SQL Database "Login failed for user" in application, but works fine in SSMS
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 ...
- 171
7
votes
MySQL connection string using password
You looking for Uniform Resource Identifier (URI) scheme, and the syntax is:
mysql://username:password@host:port/database/tablename
See the MySQL documentation
- 197
7
votes
What is the difference between a connection and a session?
Connection represents connection to the server over a network or locally through shared memory .
A session represents a user process within SQL Server.
A connection may be linked with zero or more ...
- 71
7
votes
How to solve "Can't connect to local MySQL server through socket" error?
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 ...
- 71
7
votes
Accepted
What reason could cause AWS RDS connections to spike
If you run show processlist; it'll show you all the connections that are running against your DB.
Running show status like 'Conn%' can show how many connections are active
You may think that a high ...
- 2,101
7
votes
How to deny access to SQL Server to certain login over SSMS, but allow over .Net SqlClient Data Provider
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 ...
- 69.4k
6
votes
Accepted
Find source of spids that show no hostname
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....
- 10k
6
votes
Always On Availability Groups with Encrypted Connections
First for an AG you need to Force Protocol Encryption on each instance participating in the AG. With an FCI there is only one instance.
Also with an AG clients can connect to the AG Listener, and ...
- 47.3k
6
votes
What reason could cause AWS RDS connections to spike
In your RDS, first check all IOPS metrics.
RDS provide a certain quantity of IOPS, and if your RDS Instance have 10GB SSD, probably your IOPS quantity is 100 IOPS (default), this value increase ...
6
votes
Accepted
Are CNAME aliases for connection strings a good idea?
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 ...
- 7,974
6
votes
Accepted
Record MultiSubnetFailover value at connection?
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 ...
- 47.3k
5
votes
Finding the application behind ".net sqlclient data provider"
sp_who2 (MSDN) is always a good start, and you can query the sys.processes table or sys.dm_exec_connections (MSDN) DMV.
Alternatively something like Adam Machanic's sp_WhoIsActive can help find ...
- 1,656
5
votes
Accepted
No DAC connection could be established on the default port. Make sure DAC is enabled
From Using a Dedicated Administrator Connection:
By default, the connection is only allowed from a client running on the server. Network connections are not permitted unless they are configured by ...
- 181k
5
votes
Is it normal to constantly have 30+ connections to ‘master’ database in MS SQL Server 2012?
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 ...
- 181k
5
votes
Database Connection vs Raw TCP Connection
Oracle
It is a little-known fact, that in Oracle, one can have 0, 1 or even more sessions in the very same TCP connection.
This is discussed in the book Expert Oracle Database Architecture (ISBN 978-...
- 40.9k
5
votes
MSSQL over the internet but we connect over RDP
I wrote a blog post about the perils of having your SQL Server exposed to the internet.
You should almost never allow internet access directly to SQL Server. From your description, it sounds like ...
- 70.3k
5
votes
Queries on large database kill connection to the server, works with LIMIT
"signal 9" indicates this is almost certainly the Out Of Memory killer. You could look in /var/log/kern.log or whatever it is called on your system to verify. Running out of disk space on temp files ...
- 40.6k
5
votes
Why would I download CA certificate when starting a managed Postgres cluster on DigitalOcean?
To elaborate on Laurenz's answer, if someone can intercept your internet traffic intended for Digital Ocean they could set up a malicious PostgreSQL server that you would connect to unwittingly and ...
- 40.6k
5
votes
Accepted
How much resources does a sleeping connection require in SQL Server?
I am trying to find out if these sleeping connections can have a meaningful impact on the SQL Server performance.
Generically, if the word performance here is used to discuss only executing queries ...
- 34.6k
5
votes
Why does killing a SQL Connection allow a reconnect with SSMS
I have tried to kill spids that fit this criteria and then all they have to do is run a query and it reconnects.
If a client is allowed to connect, it's allowed to connect. Killing their session or ...
- 34.6k
Only top scored, non community-wiki answers of a minimum length are eligible