Data Source setting in Connection String

Question

Consider these two connection strings that are different only by their Data Source settings:

Data Source=OEM-PC\SQLEXPRESS;Initial Catalog=<databasename>;
Integrated Security=False;Persist Security Info=False;
User ID=<userid>;Password=<password>;Connect Timeout=30

Data Source=.;Initial Catalog=<databasename>;
Integrated Security=False;Persist Security Info=False;
User ID=<userid>;Password=<password>;Connect Timeout=30

Why is it that when I use the first, I get thrown the error

Login failed for user Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: ]

I'm using SQL Server 2008 Express and the server is configured for mixed authentication and I've tripled check that using

(a) master.dbo.xp_instance_regread,
(b) SERVERPROPERTY() and
(c) master.sys.xp_loginconfig.

Also, enabling the sa login doesn't make any difference.

Let me know if additional data is required. Thanks.

Solution

Sounds like you have several different instances of SQL Server installed.

The default one (also accessed using .) has SQL Authentication setup and enabled, where the SQLExpress instance doesn't.

Use SQL Server Configuration Manager to find out what instances you have and to configure them correctly.

Comments

[Oded]

At a guess, you have several SQL Server instances installed, not just one. The default one (also accessed using .) has SQL Authentication setup and enabled, where the SQLExpress instance doesn't.