FFS Connection String!!! ARRRGGG!!!

Morpheus

DOT NET is wrecking my head.

developing ASPWebForm app with VB.NET

Database = MS SQL Server 2000

connection strings work on ONE server
but if i try to set up a connection string for a second server the exact same way it refuses to work for the second server.

One server is my own development server the other is my work mates server.

Database1 is a database we both have on our servers, its the exact same structure, basically "PS-PhilsPC-W2K" will be the Live server, mines the development one.

obviously ive changed the data source and databse names to protect my companys data etc....

heres the one that works...

Me.SqlConnection1.ConnectionString =
"workstation id=""PS-MyPC-W2K"";
packet size=4096;
integrated security=SSPI;
data source=""PS-MyPC-W2K"";
persist security info=False;
initial catalog=Database1;
Trusted_Connection=yes"


Heres the one that doesnt...

Me.SqlConnection1.ConnectionString =
"workstation id=""PS-MyPc-W2K"";
packet size=4096;
integrated security=SSPI;
data source=""PS-PhilsPc-W2K"";
persist security info=False;
initial catalog=Database1;
Trusted_Connection=yes"


regards etc...

Morpheus

heres the error page im getting....

Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

Source Error:


Line 44:
Line 45: ' Fill the project table dataset
Line 46: sqlDAProjectTbl.Fill(dsProjectTbl)
Line 47:
Line 48: ' Bind a control to the dataset


Source File: C:\Projects\VBDotNet\Production\SpellCheckTracker\MainForm.aspx.vb Line: 46

Stack Trace:

[SqlException: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.]
System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction)
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction)
System.Data.SqlClient.SqlConnection.Open()
System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection connection, ConnectionState& originalState)
System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
SpellCheckTracker.MainForm.Page_Load(Object sender, EventArgs e) in C:\Projects\VBDotNet\Production\SpellCheckTracker\MainForm.aspx.vb:46
System.Web.UI.Control.OnLoad(EventArgs e)
System.Web.UI.Control.LoadRecursive()
System.Web.UI.Page.ProcessRequestMain()

Morpheus

simplest way i can ask this question is....

How do i connect to a remote SQL server in .NET ???????

Kernel32

This is your problem...

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

Your connection string is setup for trusted connections, which is integrated security, which means SQL Server is using the windows account information to figure out who you are. On your local Sql Server its probably all setup fine because it trusts the account being use to connect to it, when you try to connect to your friends it doesn't trust the account.

The easiest way around this is to setup both sql server instances for mixed mode security, create a user on each sql server instance with the same name and password. Your connection string when then look something like this for your PC

server=PS-MyPC-W2K;uid=myuser;pwd=mypassword;database=Database1;Connect Timeout=60

and like this for connecting to your friends PC.

server=PS-PhilsPc-W2K;uid=myuser;pwd=mypassword;database=Database1;Connect Timeout=60

This isn't the most secure setup for a live application though so I urge you to do some research on integrated security between a .Net application and SQL Server.

amen

what you could do is to create a group that has access to the database and add your your friends login to this group. That way you can leave integrated security on
as anside you should make sure that the default sa password of blank has been changed
you might also want to make sure your SQL server is running the latest patches
(there is a known security issue regarding SQL server and port 1431(?))