Connection failed - SQL Server Error 772 - TCPIP Socket

Question: I've upgraded an application with a built - in Database API . When attempting to establish a SQL Server database connection this error appears - Connection failed - SQL Server Error 772 - TCPIP Socket Upon investigation the application was using the native drivers attempting to connect to a SQL Server 2016 \ Windows 2016 . As part of the testing I downloaded the ODBC 13.1 SQL Server drivers - independent of the application and tested a DSN connection to the same SQL Server - and it connected OK. I then created a DSN with native drivers and the... Read more →

TLS , SQL Server and powershell cmdlet - Get-TLSCipherSuite

Question : I have an issue from a developer about an error on the app which may be related to TLS version support by SQL Server From what I can see this could be related to a TLSV1.0 version issue. Does SQL2016 support TLSV1.0 ? how can I view which cipher suites are available on both the server hosting SQL Server and on the desktop connecting to SQL Server Answer: Microsoft SQL Server 2016 supports TLS 1.1 and TLS 1.2 . SQL Server 2016 and SQL Server 2017 on Windows versions ship with TLS 1.0 to TLS 1.2 support. You... Read more →

SQL Server database roles and GDPR

Corporate Audits and other regulatory initiatives such as GDPR , it is increasingly important to apply solid database access security practices. Users should only be able to view approved data . Avoid applying wider roles to users , such as db_owner when all the user needs is some access to 2 tables and a stored procedure. One of the tools available to the DBA is the database role. The principle of the database role is basic , and any sql server database comes with a set of default database roles such as db_datareader and db_owner. Always using the default database... Read more →

How to search a different Active Directory Domain with Powershell Get-ADGroupMember

Question: I'm using the Powershell Get-ADGroupMember to iterate through various Groups and return the members. This is working OK - as long as I'm searching in AD groups which are on the same domain server as the server I'm currently logged on. For example - I've logged onto the domain : mydomain1.admin.net . I have authority to check AD groups in this domain. But if I then attempt to do a search on another domain e.g mydomain2.admin.net , even though the domain is in the same AD farm , the Get-ADGroupMember will not find the group. To make things more... Read more →

How to check a SQL Login is part of the SYSADMIN role

Question: As part of a regular SQL Server Violations reporting and checking any vulnerabilities on SQL Servers, I run regular scans and report on elevated server roles. What is an efficient method to identify if a login is using the sysadmin role? Answer: I use the IS_SRVROLEMEMBER function , which allows you to check on various server roles. As well as sysadmin , it is possible to also check on serveradmin,dbcreator,setupadmin,bulkadmin,securityadmin,diskadmin and processadmin. The IS_SRVROLEMEMBER returns either a value of 1 or 0. 1 = the login is a member of the role 0= the login is not a member... Read more →