jpegvarn
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?
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?
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.
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.
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.
if both or if Windows Auth
Then use you windows user name password and not sa.
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.StoredProcedur e;
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.StoredProcedur
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)?
Is a domain (AD) required when I have two different machines in this environment (application server, database)?
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.StoredProcedur e;
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.StoredProcedur
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi TheLearnedOne,
I suggest PAQ and a 3 way point split.
I suggest PAQ and a 3 way point split.
you need to use the correct connection string, what are you using? take a look at www.connectionstrings.com
Cheers!