Troubleshooting Microsoft SQL Server Error 18456, Login failed for user

Reading Time: 4 minutes

Login errors with Microsoft SQL Server (MSSQL) are a fairly common issue and can be easily solved with some basic troubleshooting steps. Before we dig in, let’s take a look at the details of the error to try and determine the cause.

Solutions to Microsoft SQL Server Error 18456

Sometimes, the error presents as “login failed for user ‘<username>’,” this information will help us as we identify the user we need to troubleshoot. From the message, we’ll know the error number as a reference to search for next steps. In this case, it is Microsoft SQL Server, Error: 18456.

Other times, we may only see “Microsoft SQL Server Error 18456” along with severity and state number. On its own, a state number might not mean much, yet it can offer more details as to what is wrong and where to look next.

These states of the error, 18456, are the most common. The descriptions and potential solutions offer a quick explanation and potential troubleshooting guide.

For Microsoft SQL Server Error 18456, there are several solutions for solving the issue.

Step 1:  Log In with Remote Desktop

The troubleshooting and solutions require you to login to the server or at least be able to make a Windows Authentication connection to MSSQL using Microsoft SQL Server Management Studio. The most common and easiest method is to connect directly to the server with a Remote Desktop Connection. If you need more information about Remote Desktop Connection, these Knowledge Base articles will help you get connected:

Step 2: Run Microsoft SQL Server Management

Once you are logged into the server, you’ll want to run Microsoft SQL Server Management Studio (SSMS). SSMS is the tool best suited to configure, manage, and administer MSSQL.

When you start SSMS, you will be asked to log in to the server. By default, most MSSQL servers have Windows Authentication enabled, meaning you must log in with the Windows Administrator or the account specified as the SQL Administrator when MSSQL was installed and configured.

In addition to Windows Authentication, MSSQL supports SQL Server Authentication. Depending on the version of MSSQL and how it was installed and configured, you may or may not have SQL Server Authentication enabled by default.

Step 3: Checking the Server Authentication Mode

Once we login to SSMS using Windows Authentication, we need to check the security settings to confirm whether MSSQL is set up to allow both Windows and SQL Authentication.

In SSMS, right-click the Server Name at the top of the Object Explorer window and choose Properties.

Next, click the Security page.

If you find Windows Authentication is the only mode configured, this is the likely cause of Error 18456, Login failed for user ‘<username>’.

Setting the Server authentication mode to allow SQL Server and Windows Authentication, you will be able to login to MS-SQL with a SQL user and password or a Windows user and password. After making this change, you will need to restart the SQL Server service.

 

Step 4: Restart the SQL Service

In SSMS, right-click the Server Name at the top of the Object Explorer window and choose Restart to apply the new authentication mode settings.  

In the above example, Windows Authentication mode was the only mode configured, and the Error 18456 occurred because the user ‘sa’ is a SQL user and SQL Server Authentication was not permitted.

 

Step 5: Checking SQL User Permissions

As we check the SQL user permissions, we need to answer the following questions:

  • Is the user allowed to log in?
  • Does the user have a valid password set up?
  • Does the user have the needed permissions for access to the desired database?

In SSMS Object Explorer, expand Security, Logins. Locate the user that was failing to log in. A red x on the user indicates this user has login disabled.

To allow the user to login, right-click the user and choose Properties, then click the Status page. Enabling login for the user and click OK.

After refreshing the list user logins, we can confirm the user no longer has a red x present. This should allow the user to log in. In this example, the SQL user ‘sa’ failed to log in because there was no permission to log in. 

Continuing with user troubleshooting, right-click the user and choose Properties, then click the General page. Here you can enter a new password and then enter the confirmation password. Click OK to save the new password. We set a new password for the user so that we are certain of the password when we attempt to log in.

Step 6: Mapping the User to the Database

Our last step in troubleshooting a user is to check user mapping to verify the user has access to the desired database and to set or verify their role for the database. Right-click the user and choose Properties, then click the User Mapping page. Select the Database from the list of databases. From the database role memberships, select the desired/required memberships. Click OK.

In this example, we mapped the user ‘ProdX709’ to the database Production X709.2019 and granted them database role db_owner. In many cases, you only need a user to have db_datareader and db_datawriter roles to be able to read and write to the database.

 

In this troubleshooting article, we learned how to identify specifics of Error 18456 to help us track down the root cause of the issue. Still looking for support?  Our MSSQL database solutions come with assistance from our technical support team. Find out how our high-availability database can work for you!

Kubernetes RBAC Authorization

Reading Time: 4 minutes

What is RBAC?

Kubernetes Role-Based Access Control or the (RBAC) system describes how we define different permission levels of unique, validated users or groups in a cluster. It uses granular permission sets defined within a .yaml file to allow access to specific resources and operations.

Starting with Kubernetes 1.6, RBAC is enabled by default and users start with no permissions, and as such, permissions must be explicitly granted by an admin to a specific service or resource. These policies are crucial for effectively securing your cluster. They permit us to specify what types of actions are allowed, depending on the user’s role and their function within the organization.

Prerequisites for using Role-Based Access Control

To take advantage of RBAC, you must allow a user the ability to create roles by running the following command:

root@test:~# kubectl create clusterrolebinding cluster-admin-binding \
--clusterrole cluster-admin --user

Afterwards, to start a cluster with RBAC enabled, we would use the flag:

--authorization-mode=RBAC

 

The RBAC Model

Basically, the RBAC model is based on three components; Roles, ClusterRoles and Subjects. All k8s clusters create a default set of ClusterRoles, representing common divisions that users can be placed within.

The “edit” role lets users perform basic actions like deploying pods.
The “view” lets users review specific resources that are non-sensitive.
The “admin” role lets a user manage a namespace.
The “cluster-admin” allows access to administer a cluster.

Roles

A Role consists of rules that define a set of permissions for a resource type. Because there is no default deny rules, a Role can only be used to add access to resources within a single virtual cluster. An example would look something like this:

kind: Role
apiVersion: rbac.authorization.domain.com/home
metadata:
namespace: testdev
name: dev1
rules:
- apiGroups: [""] # "" indicates the core API group
resources: ["pods"] verbs: ["get", "watch", "list"]

In this case, the role defines that a user (dev1) can use the “get”, “watch” or “list” commands for a set of pods in the “testdev” namespace.

ClusterRoles

A ClusterRole can be used to grant the same permissions as a Role but, because they are cluster-scoped, they can also be used to grant wider access to:

  • cluster-scoped resources (like nodes)
  • non-resource endpoints (like a folder named “/test”)
  • namespaced resources (like pods) in and across all namespaces. We would need to run kubectl get pods --all-namespaces

It contains rules that define a set of permissions across resources like nodes or pods.
An example would look something like this:

kind: ClusterRole
apiVersion: rbac.authorization.k8s.io/v1
metadata:
# "namespace" omitted since ClusterRoles are not namespaced
name: secret-reader
rules:
- apiGroups: [""] resources: ["secrets"] verbs: ["get", "watch", "list"]

The default ClusterRole command looks like this:

root@test:~# kubectl create clusterrole [Options]

A command line example to create a ClusterRole named “pod“, that allows a user to perform “get“, “watch” and “list” on a pod would be:

root@test:~# kubectl create clusterrole pod --verb=get,list,watch --resource=pod

 

RoleBinding

A RoleBinding is a set of configuration rules that designate a permission set. It binds a role to subjects (Subjects are simply users, a group of users or service accounts). Those permissions can be set for a single namespace (virtual cluster), with a RoleBinding or, cluster-wide with a ClusterRoleBinding.A RoleBinding is a set of config rules that designate a permission set.

Let’s allow the group “devops1” the ability to modify the resources in the “testdev” namespace:

root@test:~# kubectl create rolebinding devops1 --clusterrole=edit --group=devops1 --namespace=dev rolebinding "devops1" created

Because we used a RoleBinding, these functions only apply within the RoleBinding’s namespace. In this case, a user within the “devops1” group can view resources in the “testdev” namespace but not in a different namespace.

ClusterRoleBindings

A ClusterRoleBinding defines which users have permissions to access which ClusterRoles. Because a “role” contains rules that represent a set of permissions, ClusterRoleBindings extend the defined permissions for:

  • unique namespace in resources like nodes
  • resources in all namespaces in a cluster
  • undefined resource endpoints like ”/foldernames”

The default ClusterRoles (admin, edit, view) can be created using the command:

root@test:~# kubectl create clusterrolebinding [options]

An example of creating a ClusterRoleBinding for user1, user2,and group1 using the cluster-admin ClusterRole

root@test:~# kubectl create clusterrolebinding cluster-admin --clusterrole=cluster-admin --user=user1 --user=user2 --group=group1

 

ConfigMaps

What is a configMap?

A configMap is a resource that easily attaches configuration data to a pod. The information that is stored in a ConfigMap is used to separate config data from other content to keep images/pods portable.

How Do I Create A ConfigMap?

To create a configmap, we simply use the command:
kubectl create configmap <map-name> <data-source>

Let’s create a default.yaml file to create a ConfigMap:
kubectl create -f default.yaml /configmaps/location/basic.yaml

Basic RBAC Commands For Kubectl

Note
These commands will give errors if RBAC isn’t configured correctly.

kubectl get roles
kubectl get rolebindings
kubectl get clusterroles
kubectl get clusterrolebindings
kubectl get clusterrole system:node -o yaml

 

Namespaces

Namespaces are used to define, separate and identify a cluster of resources among a large number of users or spaces. You should only use namespaces when you have a very diverse set of clusters, locations or users. They are used in settings where companies have multiple users or teams that are spread across various projects, locations or departments. A Namespaces also provides a way to prevent naming conflicts across a wide array of clusters.
Inside a Namespace, an object can be identified by a shorter name like ‘cluster1’ or it can be as complex as ‘US.MI.LAN.DC2.S1.R13.K5.ProdCluster1’ but, there can only be a single ‘cluster1’ or a ‘US.MI.LAN.DC2.S1.R13.K5.ProdCluster1’ inside of that namespace. So, the names of resources within a namespace must be unique but, not spanning namespaces. You could have several namespaces which are different, and they can all contain a single ‘cluster1’ object.

You can get a list of namespaces in a cluster by using this command:

root@test:~# kubectl get namespaces
NAME STATUS AGE
cluster2dev Active 1d
cluster2prod Active 4d
cluster3dev Active 2w
cluster3prod Active 4d

Kubernetes always starts with three basic namespaces:

  • default: This is the default namespace for objects that have no specifically identified namespace (eg. the big barrel o’ fun).
  • kube-system: This is the default namespace for objects generated by the Kubernetes system itself.
  • kube-public: This namespace is created automatically and is world readable by everyone. This namespace is primarily reserved for cluster usage, in case that some resources should be visible and readable publicly throughout the whole cluster. The public aspect of this namespace is only a convention, not a requirement.

Finally, the essential concept of role-based access control (RBAC) is to ensure that users who require specific access to a resource can be assigned to those Roles, Clusterroles, and ClusterRolebindings as needed or desired. The granularity of these permission sets is structured and enabled to allow for increased security, ease of security policy modification, simplified security auditing, increased productivity (RBAC cuts down on onboarding time for new employees). Lastly, RBAC allows for increased cost reduction via removing unneeded applications and licensing costs for less used applications. All in all, RBAC is a needed addition to secure your Kubernetes infrastructure.

Ready to Learn More?

Reach out to one of our Solutions team via chat to decide if a Private Cloud service from Liquid Web will meet your Kubernetes needs!

Remote Desktop Users Group

Reading Time: 4 minutes

The most common way to remotely manage a Windows server is through Remote Desktop Protocol. By default, Liquid Web’s Windows servers only allow the members of the administrators’ group remote desktop access. However, the Remote Desktop Users group grants its members access to securely connect to the server through RDP (Remote Desktop Protocol) as well.

This article will go over the basics of the Remote Desktop Users group. By the end, you will be able to add users to the group, understand permissions, and basic user management.

 

Pre-flight

The information below covers methods to configure the Remote Desktop Users group for Windows Server 2012 through Windows Server 2016 on any Liquid Web Windows server. As a valued customer, if you do not feel comfortable performing these steps independently, please contact our support team for additional assistance. Liquid Web support is happy to walk you through the steps and answer any questions you may have.

 

Managing Local Users and Groups

Users and groups on Windows servers are managed in a number of different ways, but the most user-friendly way is through the Local Users and Groups interface. There are several ways to open the interface. However,  the easiest is to run “lusrmgr.msc”. Lusrmgr.msc can be launched by searching the start menu, command line, or through a run dialog. These methods allow you to find users and groups easily.

Note
To manage local users and groups, you will need to be logged in with a user that has the proper permissions to do so. This is most commonly a user that is already a member of the Administrators group.
Within a windows server type in lusrmgr.msc into the search bar to locate Users where you can find existing users and groups.

 

User Management

Once you open the Local Users and Groups interface, you will see two folders on the left, one for Users, and one for Groups. By selecting Users, you will see a full list of local users on the server. You can also see a variety of related tasks by right-clicking Users, Groups, a user’s name, or a blank area of the middle pane.

There are several ways to add a new user through the Local Users and Groups interface. These methods all result in the same “New User” dialog box opening where you can then configure a Username, Password, and other options. Choose one of the options below to create a new user:

  • With the Users folder selected in the left pane, click the Action menu, then select “New User…”.
  • With the Users folder selected in the left pane, click “More Actions” from the right- hand pane, then select “New User…”.
  • Right-click the Users folder, then select “New User…”.
  • With the Users folder selected in the left pane, right-click in a blank area of the middle page, then select “New User…”.

Once you have created a new user, or have identified the username of the existing user, you are ready to assign that user to a Group. Users assigned to a group are known as group members.

 

Group Management

As with user management, group management can also be performed in several ways. The options below cover several of the most common ways to assign a new member to the Remote Desktop Users group:

  • Select the Users folder from the left pane of the Local Users and Groups interface, open the Users Properties window by double-clicking the user, select the “Member Of” tab, then click “Add…”. Now type “Remote Desktop Users” in the text box and click OK.
  • Select the Groups folder from the left pane of the Local Users and Groups interface, double-click the “Remote Desktop Users” group, click “Add…”, enter the user’s name in the text box and click OK.
  • Open the system settings by right-clicking the start menu and selecting “System”, choose “Advanced system settings”, select the “Remote” tab, click the “Select Users…” button then click the “Add” button. Now enter the user’s name in the text box and click OK.
  • Open the “Server Manager”, select “Local Server” from the left pane, click the blue text next to “Computer Name”, select the “Remote” tab, click the “Select Users…” button then click the “Add” button. Now enter the user’s name in the text box and click OK.
    Note
    When selecting users or groups, it is recommended to click the “Check Names” button after typing in the user or group name. If the name is underlined after clicking the “Check Names” button, then the name was identified correctly.

You can also use the “Advanced…” button when selecting users or groups instead of typing its name. Clicking the “Advanced…” button followed by the “Find Now” button will result in a list of users to select.In a windows server, by right-clicking the User folder you can do a variety of tasks like adding a new user.

 

Notes on Permissions & Security

By default, there are no members of the Remote Desktop Users group and only members of the Administrators group are allowed to connect through RDP. Members added to the Remote Desktop Users group are considered non-Administrative users. These users will be unable to perform most management tasks such as installing software, managing IIS, or rebooting the server.

If a user requires management abilities, the user will need explicit access to that task or will need to be a member of the Administrators. Please use the best practice of “least privilege” when configuring your users, groups, and permissions.

 

Test/Verify Group Membership

When configuring new user and group memberships, you should always review group membership once complete.  Reviewing group membership is most commonly performed through the Local Users and Groups interface. In addition to verifying membership, we also recommend attempting a remote desktop connection with your newest Remote Desktop Users group member. If you are unable to connect with your user, please see our Remote Desktop Troubleshooting article.

Once you have logged in with your newest member of the Remote Desktop Users group, you can further verify that groups are set up correctly by running the command “whoami /groups” from a command line. The output of this command lists the username and its associated Group names.

 

How To Change File Permissions with File Manager

Reading Time: 1 minute

This tutorial assumes you’ve already logged in to cPanel’s File Manager

  1. To change the permissions of a file, select it first. Single click on “testfile.html”.filemgr-cpanel-9-permissions-scene2_1
  2. Click on Change Permissions.filemgr-cpanel-9-permissions-scene3_1

    You will see a popup window with some checkboxes. Let’s understand this window first.
    There are three type of owners of the file.

    • User – means you.
    • Group – the users from your website, who have access to these files.
    • World – end users who access your site via a web browser.

    These are read, write and execute options. Each row give access to read, write and execute files.
    In this case, User will have access to read and write this file.
    Group will have only read access.
    And World will have Read access only.
    Please note that unless any particular script needs special permissions, a file should always have 644 permissions, and a Folder should always have 755 permissions.

  3. To set 755 permissions, just check the boxes appropriately.filemgr-cpanel-9-permissions-scene6_1
  4. Now click on Change Permissions to apply these changes.filemgr-cpanel-9-permissions-scene7_1

 

Remove Permissions for a MySQL User on Linux via Command Line

Reading Time: 2 minutes
Pre-Flight Check
  • These instructions are intended for revoking a MySQL user permissions on Linux via the command line
  • I’ll be working from a Liquid Web Core Managed CentOS 6.5 server, and I’ll be logged in as root.

Continue reading “Remove Permissions for a MySQL User on Linux via Command Line”

Grant Permissions to a MySQL User on Linux via Command Line

Reading Time: 2 minutes
Pre-Flight Check
  • These instructions are intended for granting a MySQL user permissions on Linux via the command line
  • I’ll be working from a Liquid Web Core Managed CentOS 6.5 server, and I’ll be logged in as root.

Continue reading “Grant Permissions to a MySQL User on Linux via Command Line”

Choosing a PHP Handler

Reading Time: 3 minutes

PHP is an important part of your cPanel server. One aspect of PHP’s configuration that can be overlooked is the PHP handler. The PHP handler is the specific implementation of PHP on your server that interfaces with Apache. On a cPanel server, there are four main PHP Handlers:

  • DSO
  • CGI
  • SuPHP
  • FCGI

Each has a distinct way of processing PHP code, and has its own benefits and drawbacks. In simplest terms, the decision of which PHP handler to choose comes down to your site’s specific needs.

DSO

DSO (Dynamic Shared Object) runs as an Apache module and, because it has so little overhead, it is extremely fast. However, DSO works only with Apache in non-threaded mode.

Opting for the pure speed of DSO means forgoing the performance benefits of Apache’s other Multi-Processing Modules, such as better process management and the ability to handle more connections using fewer server resources. You will need to evaluate your site’s specific needs before deciding whether to make that compromise.

By default, PHP scripts with DSO run as the Apache user “nobody”, which can have implications for security. In that configuration, scripts are not confined to a particular domain, and a malicious script could potentially gain access to files outside the site’s home directory. DSO’s standard permissions also can create some extra work when using a Content Management System (CMS). Both the security and compatibility concerns can be addressed by compiling Apache with the mod_ruid2 module, which allows scripts to run as the Linux user who owns the domain, but DSO still would require Apache to run with the single-threaded Prefork MPM.

CGI

CGI (Common Gateway Interface) is highly configurable and can run PHP scripts as the Apache user or as the Linux user which owns the domain via SuExec (which is enabled by default in WHM). However, as the slowest of the PHP handlers, it no longer is widely used. FCGI is the preferred alternative.

SuPHP

SuPHP (Single User PHP) was designed with stronger security in mind. SuPHP only executes PHP scripts as the owner of the domain, effectively isolating each user from the others.

That makes it easy to pinpoint users with resource-intensive or compromised scripts, but it also means that SuPHP is slower than either DSO or FCGI. And since a new process is created for each incoming request, a high-traffic server also could experience elevated load during a surge as processes are spawned in rapid succession. SuPHP works with any Apache MPM, though, and both the Worker and Event MPMs can help to improve resource utilization.

Because scripts run as the user, it is a common choice for sites running a CMS and doesn’t require the same changes to permissions as DSO. However, switching from DSO running as the Apache user to SuPHP would involve modifying the permissions of scripts.

FCGI

FCGI (FastCGI) was written with speed in mind, and it is fast. With SuExec enabled, FCGI runs PHP scripts as the user, offering the same benefits for CMS sites as SuPHP, but with the bonus of added speed. It also provides enhanced security compared with DSO.

It can, however, be difficult to configure, but with some optimization it is an ideal PHP handler of choice for most servers.

At Liquid Web, we have spent several months testing and optimizing FCGI on cPanel servers to achieve maximum performance and stability while minimizing its use of resources. As a result, our Fully Managed CentOS 6 and CentOS 7 templates now include FCGI with optimized, custom settings by default.

Switching Handlers

Switching from one PHP handler to another can be a detailed process, especially when changing the ownership of PHP scripts is involved. PHP is fully managed by Liquid Web’s Heroic Support® Team, so if you are considering switching handlers or would like to take advantage of our FCGI optimizations, feel free to contact us using the information below. Our Heroic Support® Team will be happy to guide you through that transition.

===

Liquid Web’s Heroic Support is always available to assist customers with this or any other issue. If you need our assistance please contact us:
Toll Free 1 (800) 580-4985
International (517) 322-0434
support@liquidweb.com
https://manage.liquidweb.com/

How To: Give a Linux User Root-level Access Using sudo

Reading Time: 2 minutes

Linux has a robust permissions system. This is a very good thing, as it enables a clear separation of roles among users, especially between the root user and your average user. Sometimes, though, you might want your average user to have some or all of root’s privileges. In Linux, this is accomplished with sudo.

Continue reading “How To: Give a Linux User Root-level Access Using sudo”

Windows: Fix Auth Errors When Modifying Users’ File Permissions

Reading Time: 1 minute

When you attempt to add or remove users from file and folder permissions within Windows Exporer, a logon box appears and does not accept your administrator username and password.  This may prevent you from modifying the permissions.

Continue reading “Windows: Fix Auth Errors When Modifying Users’ File Permissions”