?
Solved

Error connecting to MS SQL

Posted on 2005-05-17
11
Medium Priority
?
196 Views
Last Modified: 2010-03-19
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?
0
Comment
Question by:jpegvarn
9 Comments
 
LVL 10

Expert Comment

by:lengreen
ID: 14018084
Hi jpegvarn,

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

Cheers!
0
 
LVL 15

Expert Comment

by:mcmonap
ID: 14018108
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.
0
 
LVL 8

Expert Comment

by:Julianva
ID: 14018639
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.

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:jpegvarn
ID: 14020539
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;
0
 

Author Comment

by:jpegvarn
ID: 14021208
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)?
0
 
LVL 15

Expert Comment

by:mcmonap
ID: 14022002
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;
0
 
LVL 15

Expert Comment

by:mcmonap
ID: 14022032
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.
0
 
LVL 15

Accepted Solution

by:
mcmonap earned 1500 total points
ID: 14251194
Just noticed that my comment included the original connection string which it shouldn't have, it should have read something like this:

SqlConnection conn = new SqlConnection("Data Source=Mary; UID=sa; PWD=sa; Trusted_Connection=False; Database=Demo");
SqlCommand cmd = new SqlCommand( "sp_VerifyUser", conn );
cmd.CommandType = CommandType.StoredProcedure;
0
 
LVL 15

Expert Comment

by:mcmonap
ID: 14251200
Hi TheLearnedOne,

I suggest PAQ and a 3 way point split.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question