In this blog post I will show you reasons why you might be getting SQL Server error 18456 and show you may be able to solve it.

18456 Error overview

When connecting to Microsoft SQL Server Hosting and trying to use usually SQL Authentication method you may get event ID error 18456 login failed for user you provided. See below example.This usually means that your connection request was successfully received by the server name you specified but the server is not able to grant you access for a number of reasons and throws error: 18456. This eventID sometimes provides also state 1 which actually isn’t that useful as due to security reasons any error is converted to state 1 unless you can check logging on the server. Microsoft does not provide very useful message boxes so below are some explanations why you get the error.

Below is a list of reasons and some brief explanation what to do:
SQL Authentication not enabled: If you use SQL Login for the first time on SQL Server instance than very often error 18456 occurs because server might be set in Windows Authentication mode (only).

Invalid userID: SQL Server is not able to find the specified UserID on the server you are trying to get. The most common cause is that this userID hasn’t been granted access on the server but this could be also a simple typo or you accidentally are trying to connect to different server (Typical if you use more than one server)

Invalid password: Wrong password or just a typo. Remember that this username can have different passwords on different servers.

Less common errors: The userID might be disabled on the server. Windows login was provided for SQL Authentication (change to Windows Authentication. If you use SSMS you might have to run as different user to use this option). Password might have expired and probably several other reasons…. If you know of any other ones let me know.

18456 state 1 explanations: Usually Microsoft SQL Server will give you error state 1 which actually does not mean anything apart from that you have 18456 error. State 1 is used to hide actual state in order to protect the system, which to me makes sense. Below is a list with all different states.

I hope that helped you solve you 18456 error. If you know of another cause and solution than let us know and we will include it in the blog post.

A cloud server hosting company gives opportunity to buy resources that help in customization and also comes with innumerable functionalities that are gained through network in a similar manner that is offered through traditional hosting solution. It is possible to avoid all kinds of hassles that are related to hardware and minimal maintenance is required. For a cloud server, a monthly fee is paid to company.

HostForLIFE.eu is one such company that can deliver performance and dedication to your family of websites with Cloud Server. But before you know whether or not a cloud server is needed, you must know what one is. Cloud server give your site, or family of sites, all the resources necessary to navigate technical issues and keep things running smoothly. Instead of sharing personnel, infrastructure and storage space with a pack of unrelated sites, you have the freedom to control operation and mold the server to meet your needs. This can be beneficial in a number of ways.

A dedicated Cloud server has its own processor, Random Access Memory (RAM) and bandwidth capability. Dedicated Cloud servers allow you to install and run almost any program. They additionally allow other users, whom you have given access, the ability to connect to your dedicated Cloud server and use those same programs at the same time you do. This has made dedicated servers very popular amongst internet gamers. Dedicated cloud servers offer all the same features of regular dedicated hosting servers but they are intended for less serious pursuits.

According to the need of clients, Windows platform such as Windows Server 2008/2012 is rendered if using Visual Basic scripts, Active Server Pages (ASP.NET ) or Microsoft Access or Microsoft SQL database. Other important factor to be focused for rendering web hosting services is the disk space and bandwidth required. HostForLIFE.eu cloud server solution with 8 GB RAM, 2x500GB Storage Space, 5000 GB Bandwidth, 1000 MBps Connection speed with Netherlands Data Center or US Data Center.

Also, dedicated Cloud Server can keep you safe from the hazards of the web. Spyware, viruses, worms, and other nasty sounding names are waiting for an opening, planning an attack on your site that will set you back in more ways than one. If your users sense that a website has inadequate protection, it will make them think hard about returning. Hackers may also try to break in and steal financial information if you use your website for commercial purposes. Keeping the predators at bay means having a knowledge of all the latest security measures and how to implement them. Or you can just sign up for a dedicated server and not worry about it.

Different features of cloud servers 

The most important feature is that you can possibly get true value for money. You need to pay same amount as that of basic shared hosting and can get set of features that is found in a dedicated server. Cloud solution is the only way through which it is possible to save a good sum of money and enjoy different features that is necessary for business.Cloud server comes up with no downtime and this means that you will never experience error page regarding website and it always get loaded in different browsers when customers or viewers want to check. The downtime in this regard does not last for more than a few seconds and it is an amazing feature.

Next advantage is related to adding and removing of servers at any point of time and can instantly scale up solutions. It can be that you may in need of another server, which will help to handle large amount of traffic and once it is settled it is possible to scale down the server. It has turned out to be a useful user for people who are making use of cloud computing.

Benefits of Dedicated Cloud Server With HostForLIFE.eu

Faster Service

For most people what is in short supply is time. On this point a cloud server scores above dedicated servers.

Security

Cloud Server provide the highest level of security just like a dedicated cloud server, complete with customizable firewall protection and secure isolated disk space along with the benefit of sharing the cost of the server with other users yet reaping the benefits of the dedicated cloud server.

Performance

All resources can be regulated in the Cloud Server environment. One customer can not run away with a large share of the resources. Your service will run reliably and predictable.

Control

Unlike a shared server, Cloud Server allows you the control of the server. You can choose the software you want to install, RAM, processor etc.

Scalability

Dedicated cloud servers can consume time because the creation of back up takes time. Cloud servers face immense pressure as the number of clients can balloon and go out of control. But that does not happen in dedicated servers as the numbers are manageable. 

In my current project I need to query an MS SQL Server database. Unfortunately the dates are stored as a BigInt instead of a proper date datatype. So I had to find out how to do compare the dates with the systemdate, and how to get the system date. To log this for possible later use, as an exception, a blog about SqlServer. To get the system date, you can do:

(SELECT dt=GETDATE()) a

It's maybe my Oracle background, but I would write this like:
(SELECT GETDATE() dt) a

An alternative is:
select CURRENT_TIMESTAMP

I found this at this blog. Contrary to the writer of that blog I would prefer this version, since I found that it works on Oracle too. There are several ways to convert this to a bigint, but the most compact I found is:
( SELECT  YEAR(DT)*10000+MONTH(dt)*100+DAY(dt) sysdateInt
FROM
  -- Test Data
  (SELECT  GETDATE() dt) a ) utl

The way I wrote this, makes it usefull as a subquery or a joined query:

SELECT
  Ent.* ,
  CASE
    WHEN Ent.endDate  IS NOT NULL
    AND Ent.endDate-1 < sysdateInt
    THEN Ent.endDate-1
    ELSE sysdateInt
  END refEndDateEntity ,
  utl.sysdateInt
FROM
  SomeEntity Ent,
  ( SELECT  YEAR(DT)*10000+MONTH(dt)*100+DAY(dt) sysdateInt
FROM
  -- Test Data
  (SELECT  GETDATE() dt) a ) utl;

To convert a bigint to a date, you can do the following:
CONVERT(DATETIME, CONVERT(CHAR(8), ent.endDate))

However, I found that although this works in a select clause, in the where-clause this would run into a "Data Truncation" error. Maybe it is due to the use of SqlDeveloper and thus a JDBC connection to SqlServer, but I'm not so enthousiastic about the error-responses of SqlServer... I assume the error has to do with the fact that it has to do with the fact that SqlServer has to interpret a column-value of a row when it did not already selected it, that is when evaluating wheter to add the row (or not) to the result set. So to make it work I added the construction as a determination value in the select clause of a 1:1 view on the table, and use that view in stead of the table. Then the selected value can be used in the where clause.

SQL Server 2014 is the next generation of Microsoft’s information platform, with new features that deliver faster performance, expand capabilities in the cloud, and provide powerful business insights.  In this blog posting I want to give you an overview about the various performance related enhancements that are introduced.

Lock Priorities

As you might know, SQL Server gives you in the Enterprise Edition Online operations, or as I call them “Almost Online Operations”. They are almost online, because internally SQL Server still has to acquire some locks, which can lead to blocking situations. For that reason SQL Server 2014 introduces Lock Priorities, where you can control how SQL Server should react, if such a blocking situation occurs.

Buffer Pool Extensions

The idea about Buffer Pool Extensions is very easy: expand the Buffer Pool with a paging file that is stored on very fast storage, like SSD drives. The Buffer Pool Extensions are coming quite handy, if you don’t have the ability to physically add more additional RAM to your database server.

Resource Governor

Resource Governor was introduced first back with SQL Server 2008, but wasn’t really a mature technology, because you had no possibility to govern I/O operations on the storage level, and you also had no chance to limit the size of the Buffer Pool for a specific workload group. With SQL Server 2014 things are changing, because you can now throttle I/O operations. Limiting Buffer Pool usage is still not possible, but hey who knows what comes in SQL Server 2016 .

Clustered ColumnStore Indexes

One of the hottest enhancements in SQL Server 2014 is the introduction of Clustered ColumnStore Indexes , which is an amazingly new way concept how to deal with ColumnStore data in SQL Server. And in addition the Clustered ColumnStore Index can be also changed directly – without using tricks like Partition Switching.

In-Memory OLTP

With In-Memory OLTP Microsoft claims that the performance of your workload can be improved up to 100x. Awesome! Everything is now stored directly in the memory, without touching your physical storage anymore (besides the transaction log, if you want). And in addition In-Memory OLTP is based on so-called Lock Free Data Structures, means locking, blocking, latching, and spinlocking is just gone. Of course, there are side-effects and even limitations with this promising approach…

Delayed Transactions

It doesn’t matter how good the throughput of your workload is, the final barrier and bottleneck is almost every time the transaction log. Because of the Write-Ahead Logging mechanism used by SQL Server, a transaction must be always written physically to the transaction log, before the transaction is committed. When your transaction log is on slow storage, your performance and throughput will suffer. For that reason SQL Server 2014 implements so-called Delayed Transactions.

Cardinality Estimation

Cardinality Estimation  is the most important thing in a relational database, because these estimations are feeded into the Query Optimizer, whose job it is to produce a good-enough execution plan. With SQL Server 2014 Microsoft has rewritten the cardinality estimator completely from scratch to overcome some limitations based on the history of this very important component.