Hot answers tagged

16

No. While the documentation currently has the following arguably ambiguous statement about what this flag means: Password policy is checked. What it really means, and should say, is that the flag serves two purposes: The password policy might have been checked, but only if (a) the password policy was enabled at the time the password was last ...


11

Microsoft has recently revealed (without a lot of fanfare) that they will be investing in TLS 1.2 and phasing out SSL. It should be relevant to all editions of SQL Server. UPDATE 2016-01-29 : Microsoft has announced official support for TLS 1.2 in 2008, 2008 R2, 2012, & 2014. Downloads and other info can be found in KB #3135244. I blogged about a few ...


9

This may not be popular among your users, but I believe the only way you can know for sure is to force a password change for every SQL login with CHECK_POLICY = ON. This will generate a set of ALTER LOGIN commands with blank passwords, you can update the query giving them all a common password or manually update each one with individual passwords - just make ...


8

You could create a domain group that you could use to create as login and user on your SQL Server and YourDatabase. After creating the domain group, you could do something like: USE [master] GO CREATE LOGIN [DOMAIN\AllUsersForYourDatabase] FROM WINDOWS GO USE [YourDatabase] GO CREATE USER [DOMAIN\AllUsersForYourDatabase] FOR LOGIN ...


6

So the IIS on this server is intended to be externally accessible from your internal data network but the SQL Server instance on this same server is not meant to allow for external connections except for only through the web interfaces of your applications, correct? If so, then is not adding an explicit rule in the Windows Firewall on this server to allow ...


6

Is the TCP port SQL Server is listening on open globally? If so, yes I'd be concerned. If there is a password that can be brute-forced or guessed, or an exploit that allows someone to bypass authentication, eventually your database could be compromised. You'll also be vulnerable to attacks that don't require access, such as someone filling up the drive that ...


6

You simply don't write it in such a way that a user can pass in a structured WHERE clause. This is a recipe for disaster, and I bet at least half of the companies who have been exploited by SQL injection thought they were protected by checking the input for keywords, stripping out semi-colons and comments, etc. They weren't, and there will always be ways ...


6

While your question would probably be borderline duplicate of this one, I'm not real fond of the answer to the other question as it does not go into to much detail of the security risk. Database mail is configurable to include who can use the profiles/accounts created. You have the following security measures when you enable Database Mail: (Reference this ...


6

Some additional points that I have noticed is that in case you are using the backup compression feature, this feature together with TDE does not go that well. We have noticed a very minimal compression rate, almost negligible. Therefore consider this point of backup compression if you are using one. I am sure you would be aware, but just to add, TDE is ...


5

SQL Server runs locally with IIS to serve its websites The main problem I see is having sql server and IIS running on the same server. IIS serves website and SQL Server should be on a different server and only allowed connection from IIS to SQLServer. There are things that you can do to mitigate the problem: Change SQL Server port away from default ...


5

By default, no direct access is permitted to either tables or stored procedures. One must explicitly grant access in order for non-privileged users to have access. The best practice to simplify security administration is to grant permissions to roles, and control access via role membership. If you want to restrict access only via stored procedures, take a ...


4

No application should require the SA account and password to operate. However, I have installed an IT Service Management product, and during the installation process you have the option to supply the SA account credentials to allow the installer to create the DB and attach an account to the DB for the software to use. The SA account credentials are not ...


4

Please find the below 4 queries and run these queries from system database. //For seeing Full user details SELECT profile FROM dba_users WHERE username = 'SYSTEM'; //This query is used to change the password life time to unlimited ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED; //This query is used to chagne the default password. alter user ...


4

If you only need to track database access, maybe collecting schema stability locks with extended events is enough. I blogged this idea last week for a totally different reason (tracking unused objects) but the script can be adapted to fit your needs. You can find it here. Basically, it reads an extended events session using the streaming api and then ...


4

As in the other answers: you need a recent CU for TLS1.2. See: FIX: You cannot use the Transport Layer Security protocol version 1.2 to connect to a server that is running SQL Server 2014 or SQL Server 2012: Cumulative Update 1 for SQL Server 2014 SP1 Cumulative Update 8 for SQL Server 2014 Cumulative Update 1 for SQL Server 2012 SP3 Cumulative Update 10 ...


4

The /128 in the address is the NUMBER OF BITS to match... not a range. So you would want 10.8.101.0/24 (match the first 24 bits). As an additional example, if you wanted to restrict it to 10.8.101.128 to 10.8.101.255 then you'd do 10.8.101.128/25 Please see the address subsection in the documentation


4

You can join on the certificate thumbprint: use master; go select database_name = d.name, dek.encryptor_type, cert_name = c.name from sys.dm_database_encryption_keys dek left join sys.certificates c on dek.encryptor_thumbprint = c.thumbprint inner join sys.databases d on dek.database_id = d.database_id; My sample output: database_name ...


4

Since you are using SQL Server local logins, they cannot be grouped. Each login is independent and each login needs a user in the database. Where you can get some saving in specifying rights is to create roles, to which you grant, revoke, or deny rights. (Note that deny rights override grant rights.) Then use these roles to give rights to the users. ...


4

You can connect using the DAC (Dedicated Administrator Connection), and pull the password column from sys.sysowners. First, connect using: ADMIN:Server\Instance Then: SELECT password_hash = [password] FROM sys.sysowners WHERE name = N'MyAppRole'; This view is only visible when using the DAC, and the column is not exposed in the parent view that is ...


4

The Linux postgres user is entirely different from the postgres database user. It's created via the rpm package. When you're logged in as the system postgres user, it assumes the database user and database name that you're wanting to connect to is the same as the operating system user that you're attempting to connect from. That's why a simple psql works, ...


4

I've resolved my problem. Not sure if all of the hurdles are things DBAs would normally encounter, but since people are often interested in resolving the whole problem, and this seemed like a series of issues others might encounter (given the various sources of the problems), here goes: Put IIS on a separate box. This allows IIS to have TLS1.0 disabled ...


4

SQL Injection is hard to track from SQL Server side. Instead of looking at sql server, you should look at your web server IIS logs. Use Log Parser to parse your IIS Logs to track down the source of sql injection. e.g. logparser.exe -i:iisw3c -o:Datagrid -rtp:100 “select date, time, c-ip, cs-uri-stem, cs-uri-query, time-taken, sc-status from ...


3

Create a database role and add those users to the role (assuming as per the previous comment that you can't create an AD group). Grant permissions on the appropriate objects to the role. create role MyRole grant EXECUTE to MyRole -- or whatever permissions you want to give EXEC sp_addrolemember 'MyRole', 'thisUser' -- add the relevant user name ...


3

Declare variable to get current instance name and pass value to OpenROWSet. Enable Ad Hoc Distributed Queries EXEC sp_configure 'show advanced options', 1 RECONFIGURE GO EXEC sp_configure 'ad hoc distributed queries', 1 RECONFIGURE GO OPENROWSET using windows authentication to fetch data DECLARE @InstanceName VARCHAR(200), @sql NVARCHAR(MAX) SELECT ...


3

Not only is it a best practice, it's mandatory to any major compliance requirements such as PCI V2 & V3, SOX, and almost any auditor will ask about it. Yes, having them on the same network is asking for major problem. Page 5 of the PCI DSS V2: For example, there must be a clear segmentation of functions and segregation of networks with different ...


3

Lock them both (SYS and SYSTEM). You shouldn't need to use them day-to-day, nothing will break. On a day to day basis you should be using named user accounts that have SYSDBA or SYSOPER. Tom Kyte recommends doing it, so you can always blame him if something does go wrong :-)


3

SYSTEM can be locked without any difficulties. SYS is different though. You can't lock it, even if you can, you can't. SQL> show user USER is "SYS" SQL> alter user sys account lock; User altered. SQL> select account_status from dba_users where username = 'SYS'; ACCOUNT_STATUS -------------------------------- LOCKED SQL> conn / as sysdba ...


3

You could just try it out. This is logged in locally sqlplus / as sysdba: SQL> alter user mat identified by COOLPASSWORD ; User altered. SQL> select substr(sql_text,0,40) from v$sqlarea where lower(sql_text) like '%alter%' ; SUBSTR(SQL_TEXT,0,40) ---------------------------------------- select substr(sql_text,0,40) from v$sqla SQL> select ...


3

The settings that you are looking for are stored in msmdsrv.ini \Program Files\Microsoft SQL Server\MSAS12.MSSQLSERVER\OLAP\Config\msmdsrv.ini or <your Analysis service custom path>\OLAP\Config\msmdsrv.ini Note: Above path applies for Default installation. Its a good practice to create a backup of that file, if you are changing any config value.


2

Of course you can put it up on Dropbox - or anywhere else. Depends on how secure you want your data to be? Do you want the NSA looking at it? Dropbox employees? I would put up an encrypted version of your mysqldump and securely transmit the key to your colleague - either face to face or over encrypted email, or have the password/key sent by courrier. ...



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