StrataFrame Forum

Connection String Help

http://forum.strataframe.net/Topic16380.aspx

By Howard Bennett - 5/14/2008

Not only am I new to StrataFrame AND Web Dev AND VS - but also to SQL Blush...needing a little help on the connection string.  I've copied the connection string from one of the wizards - but getting this error upon adding it to my global.asax - "Unrecognized escape sequence."

Here's what's in the Global.asax:

DataLayer.DataSources.Add(new SqlDataSourceItem("", "Data Source=SQLLR01\SQLLR01;Initial Catalog=PTPlusData;Integrated Security=True"));

Thanks again for helping the new guy out!

HB
By Trent L. Taylor - 5/14/2008

Since this a web application, you will really want to use a user versus integrated security.  That would be one problem, but in either case, an example of each is below. Smile

New SqlDataSourceItem("","server=MyServer;user id=sa;password=Mypassword;database=MyDatabase;")

or

New SqlDataSourceItem("","server=MyServer;Integrated Security=SSPI;database=MyDatabase;")
By Howard Bennett - 5/14/2008

I've changed it to this:

DataLayer.DataSources.Add(new SqlDataSourceItem("", "server=SQLLR01;Integrated Security=SSPI;database=PTPlusData"));

and get the error saying a connection could not be established.

So...I created a WinForms app - added the BO - added a textbox.  When the app started - got the connection manager.  Filled in all the appropriate items and was able to connect and save data.  I went to the program.cs in that app and tried to manually insert the connection string as above - got the same error as the above caused.

Is there a way to see what the Connection Manager used? I figure I can copy that...

Thanks again - HB
By Trent L. Taylor - 5/14/2008

That is what I was saying, unless you setup the IUSER_ account for the OS as an allowed user for the SQL Server, then you will not be able to authenticate with Integrated Security.  You will want to use an explicit user name, not integrated security.
By Bill Cunnien - 5/15/2008

If you have a named instance that you are referencing in the connection string then it will need to look like this:

DataLayer.DataSources.Add(new SqlDataSourceItem("Security", "Data Source=Server\\Instance;Initial Catalog=MySecurityDB;User ID=user;Password=pwd;Asynchronous Processing=True"));

Notice the "\\" which will allows the code to recognize the "\" character.

Hope that helps,
Bill

By Trent L. Taylor - 5/15/2008

Just FWI...SF automatically adds the Asynchronous processing tag for you as needed.  Since the DataLayer can save on multiple threads, this is a core level functionality of the framework.
By Bill Cunnien - 5/15/2008

Just FWI...

New acronym? Tongue

I'll knock those off, then.  Thanks!

Bill

By Trent L. Taylor - 5/15/2008

Oops...that is what fast typing get you...speed kills BigGrin
By Howard Bennett - 5/16/2008

Bill and Trent,

Thanks - I've changed it to look like this:

DataLayer.DataSources.Add(new SqlDataSourceItem("", "Data Source=SQLLR01\\SQLLR01;Initial Catalog=PTPlusData;Integrated Security=SSPI"));

Seems to work now.

But...a question on the user name...help me understand how this works.  I was thinking the webserver would be the one logging into the SQL Server.  As such, I was thinking I could use the Windows security instead of a user name/password scenario.  Why is it that I need a user name instead?  (I'm not arguing with you here - just wanting to understand - I really appreciate the help!!!)

HB
By Bill Cunnien - 5/16/2008

Hey HB!

It has been a while since I worked on a web application, but I think the use of an ID/password in the connection string has something to do with connection pooling.  Others will have to chime in here to flesh this out a bit.  Since the web server is client agnostic, I think the integrated security option versus security declarations is six of one, half dozen of another.  The web server should always be connecting to the data server under the same security context, so it may be better to specify that in the connection string.  The web server's security context, then, does not need to be directly involved.  The application itself maintains control.

Just rambling, here.  I doubt that my explanation is very clear. 

I need more coffee.  Hehe

Bill

By Trent L. Taylor - 5/16/2008

DataLayer.DataSources.Add(new SqlDataSourceItem("", "Data Source=SQLLR01\\SQLLR01;Initial Catalog=PTPlusData;Integrated Security=SSPI"));

Here is the deal...it would seem that you have the SQL Server instance installed on the same machine as your web server, if I were guessing, since you can use integrated security.  This brings up a lot of other security issues.  Since you web server can directly authenticate the SQL Server, this opens up a door for potential security risks.

When setting up a web server, it is generally best NOT to have it as part of the domain that the rest of your network runs on...you can setup DMZ domains and then link to other domains if you must, but the most secure environment is one to which the web server does not lie on the same network as all of the other machines.  It should look something like this:

OK...I know that there are some UML and diagram people out there that would like to hang me for this somewhat inaccurate picture...but it gets my point accross.  The DMZ/Firewall many times are the same box (i.e. a Cisco PIX firewall).  The web server is not part of the internal network, yet, the database is.  So in this case, you would DEFINITELY not want the SQL Server to be able to use integrated security which means that it can authenticate off of the OS and network credentials...big security risk!  You would want to create a secure password on the SQL Server that has the credentials you need for your application.  You would then "punch" a hole...securely...through the firewall that allows the web server to talk to the SQL Server using port 1433 (default port) and authenticate using an explicit SQL user ID.

There are many other security risks....but this could turn into a really long discussion.  Hope this helps!

By Greg McGuffey - 5/16/2008

Howard,



The issue is that in order for integrated security to work, you would need to give the IIS user (IUSR_ComputerName) access to the database server. Which would mean that EVERYONE IN THE UNIVERSE would have access to the database server.



OK, that is a bit overstated. But in general it is a bad idea to allow that user access to anything other than web pages. I do believe there is another option to setup the web site such that the user has to log into a domain when accessing the web site, in which case, each of those users would then need database access (and as I recall, this can get complicated fast too). This then kills connection pooling as Bill was indicating. It also means that you have to manage all those users on the database as well as in the domain and in the application. In general, it is waaaaaaaaaaaaay easier and safer to just have a database user that your application will use.



Hope that makes sense.
By Greg McGuffey - 5/16/2008

Wow...what Trent said! I really, really need to look and see if Trent is checking out a post before I post....
By Trent L. Taylor - 5/16/2008

LOL...not to worry...all good stuff BigGrin
By Howard Bennett - 5/16/2008

Yep - that makes sense.  Just so you'll know, our web server is not on our domain and our SQL Server is (which means it's not on the same machine as the web serverWink)

I do understand about the security issues and  - again - thank you for being helpful in educating me on all this.

I'm still testing and haven't published to the web server yet - so hadn't run into the problems you brought up.  I have changed the connection string to specify a user and (after changing the SQL Server to the "mixed" mode of authentication) can connect just fine.

Thanks for all your help...until next time!

HB