| Author |
Topic |
|
janwane
Starting Member
9 Posts |
 Posted - 2010-01-27 : 13:48:50
|
| 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
5072 Posts |
Posted - 2010-01-27 : 13:51:06
|
something like thisSELECT someFieldsFROM yourTable aJOIN otherDatabase.dbo.otherTable bOn a.pk = b.pk you'll need to make sure the login used in your connection string has select permission on the other db |
 |
|
|
parthenitin
Starting Member
1 Post |
Posted - 2010-01-28 : 00:56:59
|
| 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 databaseSELECT a.someFieldsFROM server.urdatabase.dbo.yourTable aJOIN server.otherDatabase.dbo.otherTable bOn a.pk = b.pkNitin S. PartheSoftware EnginnerPC Technoweledge Center Pvt.Ltd |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-28 : 03:40:00
|
| 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. |
|
|
|
janwane
Starting Member
9 Posts |
Posted - 2010-01-28 : 06:14:17
|
| 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); |
|
|
|
janwane
Starting Member
9 Posts |
Posted - 2010-01-28 : 06:17:51
|
| 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. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-28 : 07:14:07
|
| 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 MyOtherDatabaseand 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). |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-28 : 07:15:41
|
"I am alarmed at the idea that anyone can connect to my database with the username/password contained in my connection string"They can  |
|
|
|
janwane
Starting Member
9 Posts |
Posted - 2010-01-28 : 07:28:55
|
| 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! |
|
|
|
janwane
Starting Member
9 Posts |
Posted - 2010-01-28 : 07:50:12
|
| 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!) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-28 : 08:06:57
|
| 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!) |
|
|
|
|
Torn between two databases - feeling like a fool