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.

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. — Oded
– Oded, Commented Feb 17, 2013 at 13:48
You're quite right, but I am perplexed. I have a SQLEXPRESS instance and another MSSQLSERVER instance, so I stopped the SQLEXPRESS instance and I tried to get a valid connection string for the MSSQLSERVER. But then in that valid connection string, I can use "Data Source=.;Initial Catalog=<databasename>;Integrated Security=False;Persist Security Info=False;User ID=<userid>;Password=<password>;Connect Timeout=30" but not "Data Source=OEM-PC\MSSQLSERVER;Initial Catalog=<databasename>;Integrated Security=False;Persist Security Info=False;User ID=<userid>;Password=<password>;Connect Timeout=30" — Noble_Bright_Life
– Noble_Bright_Life, Commented Feb 17, 2013 at 14:42
I thought the proper syntax is "myServerName\myInstanceName" from here, so is there a naming convention I missed that "OEM-PC\MSSQLSERVER" wouldn't work? — Noble_Bright_Life
– Noble_Bright_Life, Commented Feb 17, 2013 at 15:27
@Oded: If you put your comment as an answer, I will tick it. You were spot on with the problem. Thanks. — Noble_Bright_Life
– Noble_Bright_Life, Commented Feb 17, 2013 at 15:41
I think I know now why "OEM-PC\MSSQLSERVER" doesn't work. According to here, "When connecting to SQL Server, we use the physical server name for the default instance and physical server name\INSTANCE Name for a named instance." Running SELECT SERVERPROPERTY ('InstanceName') on MSSQLSERVER instance returns NULL which means its the default, as described here. — Noble_Bright_Life
– Noble_Bright_Life, Commented Feb 17, 2013 at 16:00

Oded · Accepted Answer · 2013-02-17 17:47:40Z

2

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.

answered Feb 17, 2013 at 17:47

Oded

501k102 gold badges900 silver badges1k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Data Source setting in Connection String

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related