Showing posts with label Security. Show all posts
Showing posts with label Security. Show all posts

Tuesday 5 September 2023

Probabilistic Estimation of the Algebraic Degree of Boolean Functions

Recently, our cryptography paper on "Probabilistic estimation of the algebraic degree of Boolean functions" was published in Springer Journal as a result of about 3 years of research: https://lnkd.in/eyEw5pce

𝐀𝐛𝐬𝐭𝐫𝐚𝐜𝐭: 𝘛𝘩𝘦 𝘢𝘭𝘨𝘦𝘣𝘳𝘢𝘪𝘤 𝘥𝘦𝘨𝘳𝘦𝘦 𝘪𝘴 𝘢𝘯 𝘪𝘮𝘱𝘰𝘳𝘵𝘢𝘯𝘵 𝘱𝘢𝘳𝘢𝘮𝘦𝘵𝘦𝘳 𝘰𝘧 𝘉𝘰𝘰𝘭𝘦𝘢𝘯 𝘧𝘶𝘯𝘤𝘵𝘪𝘰𝘯𝘴 𝘶𝘴𝘦𝘥 𝘪𝘯 𝘤𝘳𝘺𝘱𝘵𝘰𝘨𝘳𝘢𝘱𝘩𝘺. 𝘞𝘩𝘦𝘯 𝘢 𝘧𝘶𝘯𝘤𝘵𝘪𝘰𝘯 𝘪𝘯 𝘢 𝘭𝘢𝘳𝘨𝘦 𝘯𝘶𝘮𝘣𝘦𝘳 𝘰𝘧 𝘷𝘢𝘳𝘪𝘢𝘣𝘭𝘦𝘴 𝘪𝘴 𝘯𝘰𝘵 𝘨𝘪𝘷𝘦𝘯 𝘦𝘹𝘱𝘭𝘪𝘤𝘪𝘵𝘭𝘺 𝘪𝘯 𝘢𝘭𝘨𝘦𝘣𝘳𝘢𝘪𝘤 𝘯𝘰𝘳𝘮𝘢𝘭 𝘧𝘰𝘳𝘮, 𝘪𝘵 𝘪𝘴 𝘶𝘴𝘶𝘢𝘭𝘭𝘺 𝘯𝘰𝘵 𝘧𝘦𝘢𝘴𝘪𝘣𝘭𝘦 𝘵𝘰 𝘤𝘰𝘮𝘱𝘶𝘵𝘦 𝘪𝘵𝘴 𝘥𝘦𝘨𝘳𝘦𝘦, 𝘴𝘰 𝘸𝘦 𝘯𝘦𝘦𝘥 𝘵𝘰 𝘦𝘴𝘵𝘪𝘮𝘢𝘵𝘦 𝘪𝘵. 𝘞𝘦 𝘱𝘳𝘰𝘱𝘰𝘴𝘦 𝘢 𝘱𝘳𝘰𝘣𝘢𝘣𝘪𝘭𝘪𝘴𝘵𝘪𝘤 𝘵𝘦𝘴𝘵 𝘧𝘰𝘳 𝘥𝘦𝘤𝘪𝘥𝘪𝘯𝘨 𝘸𝘩𝘦𝘵𝘩𝘦𝘳 𝘵𝘩𝘦 𝘢𝘭𝘨𝘦𝘣𝘳𝘢𝘪𝘤 𝘥𝘦𝘨𝘳𝘦𝘦 𝘰𝘧 𝘢 𝘉𝘰𝘰𝘭𝘦𝘢𝘯 𝘧𝘶𝘯𝘤𝘵𝘪𝘰𝘯 𝘧 𝘪𝘴 𝘣𝘦𝘭𝘰𝘸 𝘢 𝘤𝘦𝘳𝘵𝘢𝘪𝘯 𝘷𝘢𝘭𝘶𝘦 𝘬. 𝘐𝘧 𝘵𝘩𝘦 𝘥𝘦𝘨𝘳𝘦𝘦 𝘪𝘴 𝘪𝘯𝘥𝘦𝘦𝘥 𝘣𝘦𝘭𝘰𝘸 𝘬, 𝘵𝘩𝘦𝘯 𝘧 𝘸𝘪𝘭𝘭 𝘢𝘭𝘸𝘢𝘺𝘴 𝘱𝘢𝘴𝘴 𝘵𝘩𝘦 𝘵𝘦𝘴𝘵, 𝘰𝘵𝘩𝘦𝘳𝘸𝘪𝘴𝘦 𝘧 𝘸𝘪𝘭𝘭 𝘧𝘢𝘪𝘭 𝘦𝘢𝘤𝘩 𝘪𝘯𝘴𝘵𝘢𝘯𝘤𝘦 𝘰𝘧 𝘵𝘩𝘦 𝘵𝘦𝘴𝘵 𝘸𝘪𝘵𝘩 𝘢 𝘱𝘳𝘰𝘣𝘢𝘣𝘪𝘭𝘪𝘵𝘺 𝘥𝘵_𝘬(𝘧), 𝘸𝘩𝘪𝘤𝘩 𝘪𝘴 𝘤𝘭𝘰𝘴𝘦𝘭𝘺 𝘳𝘦𝘭𝘢𝘵𝘦𝘥 𝘵𝘰 𝘵𝘩𝘦 𝘢𝘷𝘦𝘳𝘢𝘨𝘦 𝘯𝘶𝘮𝘣𝘦𝘳 𝘰𝘧 𝘮𝘰𝘯𝘰𝘮𝘪𝘢𝘭𝘴 𝘰𝘧 𝘥𝘦𝘨𝘳𝘦𝘦 𝘬 𝘰𝘧 𝘵𝘩𝘦 𝘱𝘰𝘭𝘺𝘯𝘰𝘮𝘪𝘢𝘭𝘴 𝘸𝘩𝘪𝘤𝘩 𝘢𝘳𝘦 𝘢𝘧𝘧𝘪𝘯𝘦 𝘦𝘲𝘶𝘪𝘷𝘢𝘭𝘦𝘯𝘵 𝘵𝘰 𝘧. 𝘛𝘩𝘦 𝘵𝘦𝘴𝘵 𝘩𝘢𝘴 𝘢 𝘨𝘰𝘰𝘥 𝘢𝘤𝘤𝘶𝘳𝘢𝘤𝘺 𝘰𝘯𝘭𝘺 𝘪𝘧 𝘵𝘩𝘪𝘴 𝘱𝘳𝘰𝘣𝘢𝘣𝘪𝘭𝘪𝘵𝘺 𝘥𝘵_𝘬(𝘧) 𝘰𝘧 𝘧𝘢𝘪𝘭𝘪𝘯𝘨 𝘵𝘩𝘦 𝘵𝘦𝘴𝘵 𝘪𝘴 𝘯𝘰𝘵 𝘵𝘰𝘰 𝘴𝘮𝘢𝘭𝘭. 𝘞𝘦 𝘪𝘯𝘪𝘵𝘪𝘢𝘵𝘦 𝘵𝘩𝘦 𝘴𝘵𝘶𝘥𝘺 𝘰𝘧 𝘥𝘵_𝘬(𝘧) 𝘣𝘺 𝘴𝘩𝘰𝘸𝘪𝘯𝘨 𝘵𝘩𝘢𝘵 𝘪𝘯 𝘵𝘩𝘦 𝘱𝘢𝘳𝘵𝘪𝘤𝘶𝘭𝘢𝘳 𝘤𝘢𝘴𝘦 𝘸𝘩𝘦𝘯 𝘵𝘩𝘦 𝘥𝘦𝘨𝘳𝘦𝘦 𝘰𝘧 𝘧 𝘪𝘴 𝘢𝘤𝘵𝘶𝘢𝘭𝘭𝘺 𝘦𝘲𝘶𝘢𝘭 𝘵𝘰 𝘬, 𝘵𝘩𝘦 𝘱𝘳𝘰𝘣𝘢𝘣𝘪𝘭𝘪𝘵𝘺 𝘸𝘪𝘭𝘭 𝘣𝘦 𝘪𝘯 𝘵𝘩𝘦 𝘪𝘯𝘵𝘦𝘳𝘷𝘢𝘭 (0.288788, 0.5], 𝘢𝘯𝘥 𝘵𝘩𝘦𝘳𝘦𝘧𝘰𝘳𝘦 𝘢 𝘴𝘮𝘢𝘭𝘭 𝘯𝘶𝘮𝘣𝘦𝘳 𝘰𝘧 𝘳𝘶𝘯𝘴 𝘰𝘧 𝘵𝘩𝘦 𝘵𝘦𝘴𝘵 𝘸𝘪𝘭𝘭 𝘣𝘦 𝘴𝘶𝘧𝘧𝘪𝘤𝘪𝘦𝘯𝘵 𝘵𝘰 𝘨𝘪𝘷𝘦, 𝘸𝘪𝘵𝘩 𝘷𝘦𝘳𝘺 𝘩𝘪𝘨𝘩 𝘱𝘳𝘰𝘣𝘢𝘣𝘪𝘭𝘪𝘵𝘺, 𝘵𝘩𝘦 𝘤𝘰𝘳𝘳𝘦𝘤𝘵 𝘢𝘯𝘴𝘸𝘦𝘳. 𝘌𝘹𝘢𝘤𝘵 𝘷𝘢𝘭𝘶𝘦𝘴 𝘰𝘧 𝘥𝘵_𝘬(𝘧) 𝘧𝘰𝘳 𝘢𝘭𝘭 𝘵𝘩𝘦 𝘱𝘰𝘭𝘺𝘯𝘰𝘮𝘪𝘢𝘭𝘴 𝘪𝘯 8 𝘷𝘢𝘳𝘪𝘢𝘣𝘭𝘦𝘴 𝘸𝘦𝘳𝘦 𝘤𝘰𝘮𝘱𝘶𝘵𝘦𝘥 𝘶𝘴𝘪𝘯𝘨 𝘵𝘩𝘦 𝘳𝘦𝘱𝘳𝘦𝘴𝘦𝘯𝘵𝘢𝘵𝘪𝘷𝘦𝘴 𝘭𝘪𝘴𝘵𝘦𝘥 𝘣𝘺 𝘏𝘰𝘶 𝘢𝘯𝘥 𝘣𝘺 𝘓𝘢𝘯𝘨𝘦𝘷𝘪𝘯 𝘢𝘯𝘥 𝘓𝘦𝘢𝘯𝘥𝘦𝘳.

On Homomorphic Encryption

Probably, one of the most exciting recent developments in the field of cryptography is the emergence of homomorphic encryption which is a type of encryption that allows computations to be performed on encrypted data while it remains encrypted, in other words, without the need to decrypt it first. This means that sensitive data can be kept confidential while still being used by third-parties.

This is achieved through the use of special encryption algorithms that preserve the mathematical structures of the plaintext data, allowing meaningful computations to be performed on the encrypted data while preventing unauthorized access to the actual plaintext data (which is never exposed and remain secure).

This technology is particularly useful in situations where privacy and security are of great importance, such as in the healthcare industry, where patient data must be kept confidential, or in financial services, where sensitive data such as bank account information needs to be processed securely. 

Sunday 11 September 2022

Knowing and Protecting Your Data

Computer circuitry has crept into nearly everything we use nowadays, and almost all of which gather information from us and about us. As a result, the present is immersed in copious amounts of data, which is stored somewhere and, most of the time, not securely protected. 

Today we are almost entirely reliant on IT departments at work; practically all organisations depend on enterprise-wide applications to support numerous key business processes which create a mountain of information. Needless to say, IT infrastructure and business applications are increasingly — and usually, unnecessarily — more complex; complexity is the worst enemy of security — and sometimes, of performance too. As a consequence, we are losing more control of storage and, therefore, security. The more data we share, the bigger the security risk is. 

Companies always strive to successfully harness the power of data, but unfortunately, this is not the case when it comes to securely accessing data. There are laws about protecting information, but most people are either blissfully unaware of them or careless about them. Many of us know what could happen when our data end up in the wrong hands.

Friday 12 March 2021

Cryptography is Everywhere

Historically, cryptography was used for secret communication by exclusive sectors only — such as governments, military and spies — since it was crucial and affordable to them. They have long been aware of the consequences of their messages falling into the wrong hands; therefore, this situation has motivated the development of techniques for disguising a message so that only the intended recipient can read it. 
The huge desire for secrecy led nations, kings, and queens to make all-out efforts to ensure the security of communications by inventing the best possible secret codes and ciphers. 
A lover in Victorian times
As the public also became aware of the need to protect personal messages of a highly sensitive nature, they also became comfortable with encipherment. They began to express their cryptographic skills in a variety of ways — for example, young lovers in Victorian England were often forbidden from publicly expressing their affection, and could not even communicate by letter in case their parents intercepted and read the contents. This resulted in lovers sending encrypted messages to each other via the personal columns of newspapers, more specifically, via the classified ads. 

Friday 5 March 2021

Do You Want to Be a Cryptographer?

Alan Turing
Alan Turing
I've always been interested in information security since 2003, but it wasn't until I enrolled on the cryptography module — while studying for a Master in Advanced Computer Science in England in 2018— that I started getting more keen on the mathematical side of cryptography; perhaps it was in part because I cherish mathematics and had the right cryptography teacher. What's more, I admire Alan Turing since he strove to do good work in difficult conditions during World War II; his work saved millions of lives. All of these things together inspired me to immerse myself in the world of cryptography. 

Tuesday 15 August 2017

Altering SQL Jobs without granting SysAdmin privilege

What would you do if you were asked to grant a few users the permission for altering SQL Jobs? It is a tricky task to carry out although many of us would think that granting SQLAgentOperatorRole role might be enough, however, many years ago no sooner had I done it than I realised it did not work as expected, and now I think most of us faced up this issue at first.
The SQLAgentOperatorRole role allows user to alter SQL jobs as long as the user is the owner of the SQL job, otherwise, the user need to be SysAdmin at SQL instance level. What’s more, one of the best security practices says that nobody but DBA must be SysAdmin, and we should use Windows Authentication. Nevertheless, when it comes to owners for SQL jobs they should use 'sa' as owner which does not mean having enable that account, it should be disable. In this sense, it is highly advisable to have the disabled account “sa” as the owner of all SQL jobs and avoid granting SysAdmin privilege. So, it is of paramount importance not to use a windows user as owner of a SQL job because SQL Server will always validate windows users against the Active Directory and it is likely to get unforeseen errors during that process. 
Today I am going to share with you a stored procedure to enable users to alter SQL Jobs without the need of granting SysAdmin privilege. This stored procedure consists of a logic that will allow a specific user to take the ownership of a SQL Job so that the user can be able to alter it and after making the changes the user can change the ownership to ‘sa’ (or the original owner). This is the stored procedure that I mentioned above.

USE [msdb]
GO
CREATE PROC[dbo].[usp_change_owner_job] @jobname varchar(max), @newowner varchar(max)
with execute as owner
as
begin
    declare @old_owner varchar(max)
    select @old_owner= s.name from msdb.dbo.sysjobs j inner join sys.server_principals s
    on j.owner_sid= s.sid where j.name=@jobname

    --declare @newowner varchar(max)
    --set @newowner=ORIGINAL_LOGIN()
    EXEC msdb.dbo.sp_update_job @job_name=@jobname, @owner_login_name=@newowner

    --print @newowner
    declare @msg varchar(max)
    set @msg= 'The owner of ' +@jobname + ' job was changed from ''' + @old_owner + ''' to ''' + @newowner + ''''
    print @msg    
end

So, for instance, if you have a SQL job whose owner is 'sa' and your user is 'User2', you will not be able to alter the job until you take the ownership of it temporarily. Here are all the steps you must follow:

1. Create the stored procedure 'usp_change_owner_job' based on the code above.
2. Grant EXECUTE permission on 'usp_change_owner_job' to 'User2'.
3. Grant SQLAgentOperatorRole role to 'User2'.
4. Change the ownership of the SQL Job to 'User2' by using the stored procedure 'usp_change_owner_job'.

EXEC msdb.dbo.[usp_change_owner_job] 'BusinessJob01', 'User2'

The owner of BusinessJob01 job was changed from 'sa' to 'User2'

5. Now the User2 has the ownership of the SQL job and is now able to alter it.
6. After making the changes on the SQL Job, the 'User2' must change the ownership to 'sa'.

The owner of BusinessJob01 job was changed from 'User2' to 'sa'

The owner of BusinessJob01 job was changed from 'User2' to 'sa'

Having successfully completed all the steps will you be able to alter any SQL Job without granting SysAdmin privilege to users. That is all for now. Let me know any remarks you may have.

Friday 14 July 2017

Ports and Protocols Used by Microsoft SQL Server

Naturally, I have been asked many times about ports used by SQL Server services and to be honest sometimes I took me some time to reply because there is a great number of ports and protocols and it is not easy to remember them quickly. Not all of us have to learn everything by rote, so thinking about it, I made the decision of sharing the following lists of useful ports and protocols so that you can have them at hand when needed.

Ports and Protocols Used by Microsoft SQL Server 2000
Service / Purpose Protocol Port
Analysis Services TCP 2725
Client connections when "hide server" option enabled TCP 2433
Clients using Named Pipes over Netbios TCP 139/445
Microsoft SQL Monitor port UDP 1434
OLAP Services connections from downlevel clients OLAP Services 7.0 TCP 2393/2394
SQL over TCP ** TCP 1433
Standard URL for a report server (Reporting Services) TCP 80 HTTP /443 SSL
Ports and Protocols Used by Microsoft SQL Server 2005
Service / Purpose Protocol Port
Analysis Services connections via HTTP (default) TCP 80
Analysis Services connections via HTTPS (default) TCP 443
Clients using Named Pipes over Netbios TCP 137/138/139/445
Dedicated Administrator Connection TCP 1434 by default (local port). But this port is assigned dynamically by SQL Server during startup.
Reporting services on Windows 2003/2008/Vista (default) TCP 80
Reporting services on Windows XP SP2 TCP 8080
SQL Server 2005 Analysis Services TCP 2383
SQL Server Browser Service TCP 2382
SQL Server Integration Services (MSDTSServer) TCP 135
SQL Server Resolution Protocol TCP 1434
SQL over TCP (default instance) TCP 1433
SQL over TCP (named instances) TCP 1434 / 1954
Ports and Protocols Used by Microsoft SQL Server 2008/2012/2014/2016/2017
Service / Purpose Protocol Port
Analysis Services connections via HTTP (default) TCP 80
Analysis Services connections via HTTPS (default) TCP 443
Clustering UDP 135
Clustering TCP 135 (RPC) / 3343 (Cluster Network Driver) / 445 SMB / 139 NetBIOS / 5000-5099 (RPC) / 8011-8031 (RPC)
Database Mirroring TCP There is no default port for this service. Use the following T-SQL statements to identify which ports are in use: SELECT name, port FROM sys.tcp_endpoints.
Dedicated Administrator Connection TCP 1434 by default (local port). But this port is assigned dynamically by SQL Server during startup.
Filestream TCP 139 y 445
Microsoft Distributed Transaction Coordinator (MS DTC) TCP 135
Reporting services Web Services TCP 80
Reporting Services configured for use through HTTPS TCP 1433
Service Broker TCP 4022
SQL Server Analysis Services TCP 2382 (SQL Server Browser Services for SSAS port)
2383 (Clusters will listen only on this port)
SQL Server Browser Service (Database Engine) UDP 1434. Might be required when using named instances.
SQL Server Browser Service TCP 2382
SQL Server default instance running over an HTTPS endpoint. TCP 443
SQL Server Instance (Database Engine) running over an HTTP endpoint.  TCP 80 y 443 (SSL)
SQL Server Integration Services TCP 135 (DCOM)
SQL over TCP (default instance) TCP 1433
Transact-SQL Debugger TCP 135
Windows Management Instrumentation TCP 135 (DCOM)

That is all for now. I hope you find this post useful. Let me know any remarks you may have. Stay tuned.

Monday 19 June 2017

Error: could not obtain information about Windows NT group/user

Without any doubt, at times there is a need to use extended stored procedures in SQL Server, for instance, the following error may be raised while using 'xp_logininfo' to get information about domain account 'MyDomainMyAccount' (the account you are logged within SQL Server). The error may also appear when a SQL job, whose owner is a windows account, tries to authenticate against the Active Directory(AD) and this validation fails because of internal security reasons between SQL Server and the AD.

Error: 15404, State: 19. Could not obtain information about Windows NT group/user ‘MyDomainMyAccount’, error code 0x5.

In order to solve this error your Network Administrator has to enable ‘Allowed to authenticate’ security setting on the domain controllers computer object for the account 'MyDomainMyAccount' in the domain 'MyDomain' by following these steps:
  1. logon to the Domain Controller of domain 'MyDomain'
  2. open Active Directory Users and Computers (dsa.msc)
  3. enable the ‘Advanced Features’ under the menu ‘View’
  4. navigate to the domain controllers computer object and open the property window
  5. click on the security tab
  6. add the SQL Service account 'MyDomainMyAccount' and enable the setting ‘Allowed to authenticate’
  7. click OK to close the window
  8. repeat steps 4-7 on each Domain Controller computer object
Having done that, 'xp_logininfo' will run successfully bringing the information from the Active Directory. That is all for now, let me know any remarks you may have. Thanks for reading. Stay tuned.

Thursday 11 May 2017

ObjectExplorer error: Dedicated administrator connections are not supported

Dedicated Administrator Connection (DAC) is a type of privileged connection that we can establish to SQL Server database engine when it does not respond because of too much workload. In this context, DAC is truly useful which allow to diagnosis and solve this sort of problems without restarting the database server any longer. Its is said that it is not possible to establish a DAC connection to the Object Explorer panel via SSMS so that we can get the following error while trying do it in that way:

   Cannot connect to admin:<SQLInstanceFullName>.
   Dedicated administrator connections are not supported. (ObjectExplorer)




It is true up to a point, but it does not mean that SSMS does not support DAC connections. Therefore, the question is:  can we ONLY establish a DAC connection to SQL Server by using SQLCMD? the answer is 'NO'. We also can do it via SSMS while creating a new SQL Query, which means that instead of clicking on 'Object Explorer' and 'Connect', we must click on 'New Query' and finally write 'admin:<Full Name of SQL Instance>' on 'Server Name' text box. That is all, it couldn't have been simpler.



Having done that, we will have a DAC connection ready via SSMS. Let me know any remarks you may have. Thanks for reading. Stay tuned.

Monday 14 November 2016

How to create new Logins and Users for an AlwaysOn Availability Group

(This tip also applies for Log Shipping and Database Mirroring)

Surely, after setting up an AlwaysOn Availability Group (AG) we are going to need to create additional Logins and Users who must have access and the same permissions across the replica servers so that users can work on each server properly. At times it may end up being a confusing task as SQL Logins may not have matched to the correct SQL Users and therefore users will only access to the SQL instance but not databases in the Secondary Replicas. Do not forget that we cannot create Users directly in Secondary Replicas as they are only read-only, read intent or no access.

To begin with, we need to understand some basic concepts. Logins and Users in SQL Server are different things. Logins allow to access only to the SQL instance and perform tasks at that level such as create/alter/drop databases, alter configurations of the SQL instance, etc. whereas Users allow to get into the databases and work inside them doing delete/update/insert data, create/alter/drop tables, stored procedures, triggers, etc. So, traditionally we always need to have a Login and a User matched each other so as to have access to databases.(Since SQL Server 2012 it is possible to create SQL User without a SQL Login but this aspect is not covered in this post.)

In this context, when it comes to creating new Logins and Users not only will we need to create them in the Primary Replica, but also we have to make sure they also are created in each Secondary Replica so that Users and Applications can log on successfully with the same permissions as they have in the Primary Replica. So we might get some big issues if they are not matched correctly.

Obviously, we need to create the new Login at Primary Replica level and then give the permissions inside the databases. Consequently, ONLY the user will be replicated immediately to each Secondary Replica. So, because the Login is NOT a database object it will not be replicated (it is a server object), which means that we need to create the Login in each Secondary Replica server manually. If the Login is a Windows Login then all what we need to do is to create that Windows Login in each Secondary Replica. A Windows login only has a SID across the whole domain so it will be automatically matched to its Windows User with the same name in each database in the Secondary Replica server. We do not need to know its SID before creating it.

USE [master]
GO
CREATE LOGIN [DOMAIN\percy.reyes] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO

Now when it comes to working with SQL Logins and SQL Users we must figure out and take the correct SID of the SQL Login in order to use it to create the same SQL Login in each Secondary Replica server. Therefore, we can query the SID of the SQL Login we need by executing the following code like this.

Having got it, we can use it to create the SQL Login by indicating the correct SID. (Do not forget to use the same password and other properties too.)

USE [master]
GO
CREATE LOGIN [preyes] WITH  PASSWORD=N'P4s$Word&1', SID= 0x8AA780458D0F9E4F95D4204B8E022C83, CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

In conclusion, only after successfully following this technique will the SQL Login match automatically to the correct user inside the database. It also means that the user will be able to log on any Secondary Replica server with the right permissions inside the databases. That is all for now. Let me know any remarks you may have. Thanks for reading.

Wednesday 20 July 2016

How to query the windows users that access via windows groups from SQL Server

At times while monitoring a database server, we may need to know not only which SQL users are accessing, but also windows users for security and auditing purposes. Frankly, this task may be quite simple to get it completed when all of those users are created and visible inside the database engine. Despite this, it is not a surprise that windows users can access via windows groups which means that DBAs can not see them from SQL Server easily unless we have the manner to list them. Luckily, there is an extended stored procedure named “xp_logininfo” that we need to use to get that information. (The 'xp_logininfo' asks the Active Directory for the windows users.)
First of all, this system stored procedure takes two input parameters. The first one is the windows group name, and the second one is the value ‘members’.  For instance, it lists the windows users that access via the windows group ' MyDomain\SQLProdUsrs'. (You need to have “sysadmin” role to execute it.)
EXEC xp_logininfo  @acctname ='MyDomain\SQLPrdUsrs',  @option='members' 
Naturally, this is perfect if we only have one windows group to query. As time goes by, we are likely to get created more windows groups inside the database engine and in no time we will need to create an specific script to figure out the other windows users. Here is that code.
SET NOCOUNT ON

CREATE TABLE #WindowGroup(

    server_name varchar(100),

    account_name varchar(300),

    type char(8),

    privilege char(9) ,

    mapped_login_name varchar(300) ,

    permission_pathsysname  varchar(300)

 )

 

DECLARE @WindowGroupName varchar(max)

DECLARE @db [NCHAR](128)  

DECLARE cursor_WG CURSOR FOR SELECT [name] FROM sys.server_principals WHERE TYPE='G' 

 

OPEN cursor_WG

    FETCH NEXT FROM  cursor_WG INTO @WindowGroupName

    WHILE @@FETCH_STATUS= 0

        BEGIN

            INSERT #WindowGroup(account_name , type , privilege  ,mapped_login_name  ,permission_pathsysname   )

            EXEC xp_logininfo  @acctname =@WindowGroupName  ,  @option =  'members' 

        FETCH NEXT FROM cursor_WG INTO @WindowGroupName

    END 

CLOSE cursor_WG

DEALLOCATE cursor_WG

 

UPDATE #WindowGroup SET server_name=@@servername

select server_name AS ServerName, account_name as WindowsAccountName, Type, Privilege, mapped_login_name as MappedLoginName, permission_pathsysname as WindowsGroupName

FROM #WindowGroup 

ORDER BY permission_pathsysname,mapped_login_name

DROP TABLE #WindowGroup

 

SET NOCOUNT OFF


As you have seen, the T-SQL code is simply easy to understand. Clearly, it filters the windows groups by indicating the value ‘G’ for the Type column of the system view “sys.server_principals”, and eventually through the cursor each of them is read. Now I hope you make the most out of this script to have better visibility of the windows users accessing the databases. Thanks for reading again!

Sunday 1 May 2016

AlwaysOn AG Listener: The attempt to create the network name and IP address for the listener failed

While working on a heap of High Availability and Disaster Recovery solutions, I was challenged to deal with some complex errors that I had to overcome as fast as possible. Today’s post is going to show how we may solve one of them. I am speaking about the following error which is raised when we have to set up the AlwaysOn AG Listener:

The Windows Server Failover Clustering (WSFC) resource control API returned error code 5057.  The WSFC service may not be running or may not be accessible in its current state, or the specified arguments are invalid. 
The attempt to create the network name and IP address for the listener failed. The WSFC service may not be running or may be inaccessible in its current state, or the values provided for the network name and IP address may be incorrect. Check the state of the WSFC cluster and validate the network name and IP address with the network administrator. (Microsoft SQL Server, Error: 41009)

At times it can be quite easy to fix it, but it may become complicated as we do not have more details of the root cause, therefore, it does not give us any clue. In my experience working on this, the most common cause has to be about lacking of permission for the cluster name account so we have to make sure that this account has the 'Create Computer' and 'Read' permissions:



Once you have given right permissions and if the error is still there then you must check whether the IP Address is available to be assigned to the AlwaysOn AG Listener. It is simple to verify by making ping to IP Address which should be free. If not, ask your Administrator a new IP Address and try again.
I hope this practical post helps you. Let me know any remarks you may have. Until next post, thanks for reading!

Wednesday 9 March 2016

SQL Server Analysis Services Error: The following system error occurred: (Microsoft.AnalysisServices)

Clearly, while adding some users as Windows Administrators, not only do we do that, but also we add them as Administrator of Analysis Services (SSAS). So, if we delete them from Windows then they will become orphaned users inside SSAS. As a result of this action, not surprisingly, we are going to get the following error when we are trying to add other users to the server administrator role in SSAS via the GUI or by code.

“The following system error occurred: (Microsoft.AnalysisServices)”

This sort of error is raised at times as the orphaned users have not got a correct matching inside the Active Directory. So, only their invalid user IDs will still be registered.



What we should do to fix this issue and be able to add other users is, firstly, to remove these orphaned users and, finally, add the new ones. I hope you find this short post interesting. Thanks for reading!

Thursday 28 January 2016

Did you get this "AuthorizationManager check failed" error working with SQL Jobs and PowerShell?

Taking of PowerShell, while working on implementing SQL Jobs which execute PowerShell scripts, unexpectedly, they may begin failing without any apparent reason. So, we get this error:

AuthorizationManager check failed At line:1 char:2  + & <<<<  ‘S:myfolderscript.ps1’      + CategoryInfo          : NotSpecified: (:) [], PSSecurityException      + FullyQualifiedErrorId : RuntimeException.  Process Exit Code 1.  The step failed.

What we should do is to check PowerShell so as to make sure the ExecutionPolicy is not set to “Restricted” by executing the following command:
Get-ExecutionPolicy
If it is then set it to “RemoteSigned” or “Unrestricted” depending on your security policy.
Set-ExecutionPolicy RemoteSigned
Not only do we have to make sure that Windows Management Instrumentation service (WMI) service is enabled and running, but also we have to restart it. Only after successfully doing that will your job and script run again with no error. I do believe that this would work without a shadow of a doubt for the vast majority of cases. I hope you find this post useful. Thanks for reading.

Friday 12 December 2014

Script to Drop All Orphaned SQL Server Database Users

Today my latest tip has been published about “Script to Drop All Orphaned SQL Server Database Users” and you can read it at http://www.mssqltips.com/sqlservertip/3439/script-to-drop-all-orphaned-sql-server-database-users/
Please let me know if you have any comments or questions. Thanks for reading!

Friday 14 April 2006

How to remove 'sa' account from 'sysadmin' fixed server role in SQL Server 2000

When it comes to securing the databases, we need to have a small number of accounts with 'sysadmin' fixed server role, which means that only the DBA and someone else should be assigned to that role inside the database engine. So, I highly recommend disabling or removing 'sa' account from 'sysadmin' fixed server role in SQL Server 2000. Nevertheless, this task is not so easy to do because any change on 'sa' account is protected internally by SQL Server. Furthermore, Microsoft says that this account cannot be changed at all http://msdn.microsoft.com/en-us/library/aa905197(SQL.80).aspx.
System administrator (sa) is a special login provided for backward compatibility. By default, it is assigned to the sysadmin fixed server role and cannot be changed. Although sa is a built-in administrator login, do not use it routinely. Instead, make system administrators members of the sysadmin fixed server role, and have them log on using their own logins. Use sa only when there is no other way to log in to an instance of Microsoft® SQL Server™ (for example, when other system administrators are unavailable or have forgotten their passwords).
Because I also know that "In theory there is no difference between theory and practice. But, in practice, there is.” (Jan L.A. van de Snepscheut), I was digging deep into this issue and found out that, fortunately, what Microsoft says is true up to a point, but we can take control of it by making small changes internally. In other words, the famous 'sa' account can be changed in SQL Server 2000. Today's post is going to show how to achieve it with ease. (The demonstration is done on SQL Server 2000 Service Pack 3a.)

First of all, we do need to know that the logic to manage the 'sa' account is stored inside the 'master' system database of SQL Server. Consequently, I decided to look into the code of sp_dropsrvrolemember, sp_addsrvrolemember, and sp_droplogin system stored procedures. What I figured out is that all of them contain the following validation based on the name of the account to prevent from any change.


According to that code, only if we change that name will SQL Server allow us to make other changes on it. Likewise, I also noticed that SQL Server does not allow to delete this account if its SID is equal to '0x01', so, if I we also change that SID, we will be able to delete the account. This is what we are going to do now.

Naturally, we will get the following error when we try to remove the 'sa' account from 'sysadmin' fixed server role:

exec sp_dropsrvrolemember sa,‘sysadmin’ 

Server: Msg 15405, Level 11, State 1, Procedure sp_dropsrvrolemember, Line 40 
Cannot use the reserved user or role name ‘sa’.

Trying to drop the 'sa' account:

exec sp_droplogin sa

Server: Msg 15405, Level 11, State 1, Procedure sp_droplogin, Line 39
Cannot use the reserved user or role name ‘sa’.


In order to be able to make those changes, we firstly need to enable 'allow updates'.
exec sp_configure 'allow updates', 1  
go  
reconfigure with override 

Having done that, we can remove the 'sa2' account from 'sysadmin' fixed server role.

exec sp_dropsrvrolemember sa2, 'sysadmin'

Changing the SID to '0x02':

 update sysxlogins set sid=0x02 where name='sa2' 

Finally, only now are we able to delete 'sa2' account.

exec sp_droplogin 'sa2' 

Having successfully executed each step, we have succeeded in deleting the 'sa' account. As you have seen, it was not overly complicated to get this account deleted. On the other hand, how can we create that account again? if we try to create again the 'sa' account, we will get this error:

exec sp_addlogin 'sa'

Server: Msg 15405, Level 11, State 1, Procedure sp_addlogin, Line 49
Cannot use the reserved user or role name ‘sa’.


Do not worry about that, we can also create it again by executing this code as follow:

exec sp_addlogin 'sa2'  
go  
exec sp_addsrvrolemember sa2,'sysadmin'  
go 
update sysxlogins set name='sa' where name='sa2'
go
update sysxlogins set sid=0x01 where name='sa' 


That is all for now. I hope you find this tip helpful. Let me know any remarks you may have. Thanks for reading. Stay tuned.
HELLO, I'M PERCY REYES! — a book lover, healthy lifestyle lover... I've been working as a senior SQL Server Database Administrator (DBA) for over 20 years; I'm a three-time awarded Microsoft Data Platform MVP. I'm currently doing a PhD in Computer Science (cryptography) at Loughborough University, England — working on cryptographic Boolean functions, algorithmic cryptanalysis, number theory, and other algebraic aspects of cryptography. READ MORE