SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Torn between two databases - feeling like a fool
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

janwane
Starting Member

USA
9 Posts

Posted - 01/27/2010 :  13:48:50  Show Profile  Reply with Quote
We're a small company with web apps & (SQL) databases hosted remotely. My main db has 98 fully normalized tables. I set up a second SQL db with 1 table for the sole purpose of allowing another company to write data I need for an interface. I put the other company's "userid" as a foreign key in my Users table. Obviously to use the data in their table I've got to join their table to tables in my main database. I don't see how to do that in my ASP.NET programs because a connection string can be set for only 1 database. What is the best approach to use here? I can think of a couple solutions but thought I'd ask what is best approach at the conceptual level? Thanks.

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 01/27/2010 :  13:51:06  Show Profile  Visit russell's Homepage  Reply with Quote
something like this
SELECT	someFields
FROM	yourTable a
JOIN	otherDatabase.dbo.otherTable b
On	a.pk = b.pk


you'll need to make sure the login used in your connection string has select permission on the other db

Edited by - russell on 01/27/2010 13:52:34
Go to Top of Page

parthenitin
Starting Member

India
1 Posts

Posted - 01/28/2010 :  00:56:59  Show Profile  Reply with Quote
Is both databases are on Same Server if they are then no need to do tow connections...
U can write this query in SP whith full Name of server with database
SELECT a.someFields
FROM server.urdatabase.dbo.yourTable a
JOIN server.otherDatabase.dbo.otherTable b
On a.pk = b.pk



Nitin S. Parthe
Software Enginner
PC Technoweledge Center Pvt.Ltd
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/28/2010 :  03:40:00  Show Profile  Reply with Quote
There is no need to include the SERVER name if is the same server, or the server you are connecting to.

Also no need to include the database name if it is the database you are connected to.
Go to Top of Page

janwane
Starting Member

USA
9 Posts

Posted - 01/28/2010 :  06:14:17  Show Profile  Reply with Quote
Thank you everyone but I am confused. I had hoped this was the answer "make sure the login used in your connection string has select permission on the other db" (Russell). I interpreted this to mean "assign the same database user to the 2nd database" so I opened the admin client my web host provides and discovered I can't do that. The only thing I can do with the db user is change the password. So I'm stumped with this approach.

Regarding the second method suggested by parthenitin. Are you telling me I can connect to the second database just by citing its name WITHOUT authenticating myself with the Username & password? Isn't that the point of citing the connection string first; i.e.
conn = new SqlConnection();
conn.ConnectionString = ConfigurationManager.ConnectionStrings["HRConnectionString"].ConnectionString;
conn.Open();
then that "conn" is used in calling the SqlDataAdapter
SqlDataAdapter taa = new SqlDataAdapter(strSQL, conn);
DataTable dta = new DataTable("AvailHrs");
taa.Fill(dta);
Go to Top of Page

janwane
Starting Member

USA
9 Posts

Posted - 01/28/2010 :  06:17:51  Show Profile  Reply with Quote
Sorry, that got posted before I was finished. My point is I've opened a connection ONLY with the main database. And I am alarmed at the idea that anyone can connect to my database with the username/password contained in my connection string and I really don't believe that's what you meant. Can you clarify? Thank you.
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/28/2010 :  07:14:07  Show Profile  Reply with Quote
You connect to a SQL Server (not a database). You have a SQL server login (or use Windows Authentication - so called Trusted mode).

MSSQL has a Default database assigned to your server login, or in your connection string you specify a database to connect to. That is the database you get data from when you do "SELECT * FROM MyTable".

You can switch to a different database:

USE MyOtherDatabase

and that then becomes your current database, or you can explicitly indicate the database to use e.g. "SELECT * FROM MyOtherDatabase.dbo.MyTable"

For each SQL Server login you can specify which databases the login can connect to, and what Role the user has when connected to that database (so you could grant "God" Role on one database and "NotMuch" Role on the other).

A Role is either a system-provided one like db_DataReader (which provides Select permission on all tables) or one that you create, and Name, where you individually grant Select, update, Delete permission on each table).
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/28/2010 :  07:15:41  Show Profile  Reply with Quote
"I am alarmed at the idea that anyone can connect to my database with the username/password contained in my connection string"

They can
Go to Top of Page

janwane
Starting Member

USA
9 Posts

Posted - 01/28/2010 :  07:28:55  Show Profile  Reply with Quote
I misspoke: I meant to say "WITHOUT" as in I am alarmed at the idea that anyone can connect to my database withOUT the username/password contained in my connection string.

Regardless, I can see I must talk to my web host because the capability you describe is not available to me via the PLESK admin tool. The point is I didn't know to ask about it...always dealt with 1 db per application. Thanks so much!
Go to Top of Page

janwane
Starting Member

USA
9 Posts

Posted - 01/28/2010 :  07:50:12  Show Profile  Reply with Quote
I LOVE my web host! They've already made the requested change! Kristen and all who replied, thank you so much for telling me what was possible! Have a great day (or night!)
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/28/2010 :  08:06:57  Show Profile  Reply with Quote
If your App and SQL are on the same machine I reckon your APP will be able to connect using Trusted security. Then you can set SQL Server to NOT allow any SQL Logins, so only people with a Windows Login will be able to connect. That is much more difficult to emulate from "outside".

however, that would mean that you could not connect to SQL (on port 1144) from your PC, so you wouldn't be able to run SQL TOols from your desktop PC - you'd have to use Terminal Services to connect to the Server itself, or somesuch.

And if you are on Shared SQL hosting that may not be an option ('coz everyone else will need access, natch!)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000