asked on

Error connecting to MS SQL

Hello,

I have a webserver and database on two separate machines in a WORKGROUP - they are on different networks also.

Database = 10.0.0.2
Webserver = 10.0.1.2

Each can ping each other and I opened the firewall to allow all.

We are using an ASP application and when trying to connect to the database, I am getting the following:

"Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection."

Would this have anything to do with the fact that its in a WORKGROUP? Does it have to be in a DOMAIN for the trust relationship? I believe we are using 'sa' as the username in the application.

Any help?

lengreen

Hi jpegvarn,

you need to use the correct connection string, what are you using? take a look at www.connectionstrings.com

Cheers!

mcmonap

Hi jpegvarn,

In addition do not use the sa acocunt for app connections it is bad practice, create a user account with rights just in the database you are connecting to.

Julianva

Your SQL server is configured for Windows Authentication or SQL login ??
if both or if Windows Auth

Then use you windows user name password and not sa.

jpegvarn

ASKER

Does this look correct? I know it is bad practice to use the 'sa' account, but it should work in a testing environment, correct?

It is using SQL Server and Windows Auth.

SqlConnection conn = new SqlConnection("Data Source=Mary; UID=sa; PWD=sa; Integrated Security=SSPI; Database=Demo");
SqlConnection conn = new SqlConnection("Data Source=" + dbDSN + "; UID=" + dbUID + "; PWD=" + db_PW + "; Integrated Security=SSPI; Initial Catalog=" + dbCAT);
SqlCommand cmd = new SqlCommand( "sp_VerifyUser", conn );
cmd.CommandType = CommandType.StoredProcedure;

jpegvarn

ASKER

I failed to mention this is a ASP.NET application.

Is a domain (AD) required when I have two different machines in this environment (application server, database)?

mcmonap

IN order to use the sa account you must not use trusted security, try something like below (bearing in mind I wouldn't know my arse from my elbow in a .Net app). As Julianva mentioned you sould ensure that your sql server will accept connections using SQL authentication, you check out how to do this and change it if necessary here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_47u6.asp

SqlConnection conn = new SqlConnection("Data Source=Mary; UID=sa; PWD=sa; Trusted_Connection=False; Database=Demo");
SqlConnection conn = new SqlConnection("Data Source=" + dbDSN + "; UID=" + dbUID + "; PWD=" + db_PW + "; Integrated Security=SSPI; Initial Catalog=" + dbCAT);
SqlCommand cmd = new SqlCommand( "sp_VerifyUser", conn );
cmd.CommandType = CommandType.StoredProcedure;

mcmonap

oops, meant to add - you should not necessarily require a domain to do what you want, if you must use windows authentication you should be able to do it by synchronising usernames and passwords on the teo machines. IMO though the SQL authentication would be the preferred option.

ASKER CERTIFIED SOLUTION

mcmonap

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mcmonap

Hi TheLearnedOne,

I suggest PAQ and a 3 way point split.