Hot answers tagged

12

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

It's to do with Python's object model - there's always a way to get a reference to objects that could be unsafe. See the rexec module documentation and the restricted execution chapter of the docs for some info on the problems, as well as: https://www.researchgate.net/publication/228612669_Controlling_access_to_resources_within_the_python_interpreter http:/...


7

You'll need to determine how the logins have access to run DDL in master. The following query will return a list of logins with server-level role membership: SELECT ServerName = @@SERVERNAME , RoleName = roles.name , MemberName = members.name , IsEnabled = CASE WHEN members.is_disabled = 1 THEN 0 ELSE 1 END FROM sys.server_role_members srm ...


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 ...


6

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 C:\wwwlogs\...


6

If you need really tight control enforced at the data level. For example extensive auditing. Auditing is not much good if several users share the same account. If you have some users that have a need to access the data database directly. If security is that tight you typically don't even expose the database directly. You have a service and the client ...


6

First and foremost this is an issue of access control. Eliminate all redundant logins, and reduce permissions on the required logins to the strictly necessary minimum. Do not use 'well known' logins shared by every person with DB access, make sure every user uses its own login (eg. use Integrated Auth). It is not clear from your post whether this server is ...


6

The resolution to this issue was enabling service broker on the database as so. ALTER DATABASE [Database_name] SET NEW_BROKER WITH ROLLBACK IMMEDIATE; After enabling service broker for the database, drop users were practically instantaneous. Kin asked if the service broker was enabled in a previous comment which sent me searching in the right direction.


5

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 ...


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 ...


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 of ...


5

The challenge here is that your database access is managed by an abstraction. Instead of users connecting as themselves, they essentially take on the identity of some generalized application role. You not only lose visibility of the individual connecting, but you also lose granularity of defining different types of access for all your individual users. The ...


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

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 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

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 :-)


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

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

Simply "disable" the login: ALTER LOGIN [xyz] DISABLE; Then, if the login is a SQL Server login, you can change the password with: ALTER LOGIN [xyz] WITH PASSWORD = '$ome$ecurePa$$w0rd'; Then, re-enable the login: ALTER LOGIN [xyz] ENABLE; If the login is from Active Directory, you can still disable the account (and re-enable later), but you'll ...


4

You can grant admin access using this code: CREATE LOGIN [DOMAIN\ADGroupName] FROM WINDOWS; GRANT CONTROL SERVER TO [DOMAIN\ADGroupName]; That will allow any user who is a member of [DOMAIN\ADGroupName] to login to SQL Server without specifying a username/password, and will grant those users admin access to the entire instance, including all databases. ...


4

Just change ownership for starters. Database ownership (the owner_sid in sys.databases) not have really have much impact on day to day operations


4

DB_NAME does not work as advertised before SQL Server 2016 (where the behaviour of DB_ID is also changed). For details, see: Information disclosure with the db_name and db_id function (Connect bug report) There is a similar situation with other metadata functions, including: suser_name suser_sname suser_sid user_id database_principal_id is_rolemember ...


4

The security concern with SMTP from a database server is the potential for disclosure of sensitive information. For example, a user that has (or gains) permissions to execute sp_send_dbmail could send an email with sensitive information. Disallowing outbound access to the default SMTP port is a security measure security folks sometimes insist upon to limit ...


4

I'd talk to your hosting provider's technical support people about this and get them to explain to you the security configuration they have on the msdb database, which is where those details are stored. By itself, the list of database names likely does not indicate a security risk. I'd ensure I wasn't giving away any details in my database names. If I had ...


4

Yes. What you have will do what you want. I do question the need for UserA to have db_owner access even on database A though. Do they have a need to be able to drop the database? Run backups that could interrupt your backup chain? Change recovery model (same problem)? Generally even if a developer has complete control over a given database I would ...



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