Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server ASP
|
SQL Server ASP Discussions about ASP programming with Microsoft's SQL Server. For more ASP forums, see the ASP forum category.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server ASP section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 15th, 2004, 01:40 PM
Authorized User
 
Join Date: Apr 2004
Posts: 50
Thanks: 0
Thanked 0 Times in 0 Posts
Default connection string question

I'm new to using SQL Server and have been going through some tutorials learning the basics of creating tables, relationships, etc.

I'm developing on a testing server at my home. The actual location of a live production sql server db would be on a remote web server.

I'm still a little lost on the whole security and logins aspect of sql server. What is the difference between setting up the IUSER account under the security > logins node in Enterprise Manager or using the sa account for a database?

So, just as a starting point, I used a connection string to connect to the default Northwind db within SQL Server on my home testing server.

I used the following connection string that I created within DMX:
Dim MM_ConnNorthwind_STRING
MM_ConnNorthwind_STRING = "Provider=SQLOLEDB.1;Password=AbCdZ1;Persist Security Info=True;User ID=sa;Initial Catalog=Northwind;Data Source=DWAYNE-SERVER"

The connection works fine.

Now, if I moved the database to the remote production server with my hosting company, would I only change Data Source name to the appropriate server name for the hosting server?


Or, do I have to create an IUSER account for that db in order for it to work in a live production environment??

I hope this makes sense?

Thanks for any help.
-Dman100-


 
Old August 15th, 2004, 04:36 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Hi there,

sa Account is SQL server's in-built account that is used for administration. I wont' suggest you(as a safety measure) NOT use sa account in your connection string for the web applications. It is better to create a user that needs relavant privilege to access the database and use that in your connection string.

When you move the database to the remote production server, you will have to just change the data source name, provided that, you have all other things in place. DB name being the same, user account still exists in that server... so on... If you have things different in the remote sql server, then you need to make changes in the connection string accordingly.

Cheers!

_________________________
- Vijay G
Strive for Perfection
 
Old August 15th, 2004, 06:28 PM
Authorized User
 
Join Date: Apr 2004
Posts: 50
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Vijay,

Thanks for replying to my post. Okay, so I shouldn't use the built-in SA account that I setup when I installed SQL Server for any web applications I'm building for security reasons.

So, for example, if I'm creating a new database for a web application. Should I create the IUSER account under the security > logins node in Enterprise Manager for the new database on my testing server?

Then when I create the connection string in DMX it would be something like the following:

Dim MM_ConnNorthwind_STRING
MM_ConnNorthwind_STRING = "Provider=SQLOLEDB.1;Password=NewPassword;Pers ist Security Info=True;User ID=IUSR_DWAYNE-SERVER;Initial Catalog=NewDatabaseName;Data Source=DWAYNE-SERVER"


When I detach the database and copy the files and send to the remote web server to be re-attached, will the IUSR_ServerName change automatically? I assume it will?

Then all I need to change will be the server name for the remote web server in the ID and Data Source in the connection string? Is that correct?

Thanks again for your help! I really appreciate it.
-Dman100-

 
Old August 15th, 2004, 06:53 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Hi Dman,

IUSR_DWAYNE - is that you are using your web server's windows user account? You got to create an sql login(using EM) with required privilege and set its default database as the database which you wanted it to use. So that when that login is used, it always uses the default database set.

Quote:
quote:When I detach the database and copy the files and send to the remote web server to be re-attached, will the IUSR_ServerName change automatically? I assume it will?
If I am right, the login that you created in your test environment, will not go along with the DB. You will have to create similar one in the remote server once for all. Coz, the login related details are stored in master database. So detach and re-attach of your db will not carry the login details too, along with it. Anyway, you would be given access to the remote sql server(atleast to get connected from your web server), so you can create login using EM again.

Quote:
quote:Then all I need to change will be the server name for the remote web server in the ID and Data Source in the connection string? Is that correct?
I assume that you mean to change is as IUSR_REMOTESERVERNAME. If so, Is the webserver and SQL server going to be running on the same system? In that case still you got to
add that login in your remote sql server using EM and make change in the connection string too.

Hope that explains.
Cheers!

_________________________
- Vijay G
Strive for Perfection
 
Old August 16th, 2004, 12:25 AM
Authorized User
 
Join Date: Apr 2004
Posts: 50
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Vijay,

If I can ask a couple of more questions, to make sure I understand correctly.

To clarify, the machine name for my testing server is "DWAYNE-SERVER". I set this up when I installed Windows 2000 Server.

Wouldn't the default guest Internet account be IUSR_DWAYNE-SERVER for my testing server?

So, when I create a new database in EM I would go to the security > logins node and set the login to "IUSR_DWAYNE-SERVER" which is the default Internet guest account for my testing server. Is that correct? Or, do I need to create and use another login account?

The next step would be to copy or move the database to the remote production web server. If I understand correctly, the IUSR account is the default Internet guest account for every web server. Is that right? If so, wouldn't I just change the name for the server from
IUSR_DWAYNE-SERVER to IUSR_REMOTEWEBSERVERNAME?

Wouldn't the Internet guest account IUSR_REMOTEWEBSERVERNAME be the login account used for web application databases? Or do you create and use some other login account?

Let me know if I've explained this correctly? The login and security setup for SQL SERVER is still confusing to me.

I really appreciate your help.
Thanks,
-Dman100-

 
Old August 16th, 2004, 01:17 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Hi there,

So far, I haven't used IUSR account for accessing the SQL server. I always have created an sql server account(something like sa, with less privilege) and used the same in web applications for connection string. I am not sure why you wanted to use IUSR_<ComputerName> as the account to access sql server. Just like sa, you can create another account in sql server and use that in connection string. Whatever is being used in your test environment, once you port that to the production server, you would have to create the same user in production environment too and that would not require a change in connection string even.

Eg: Once you are done with test server, backup the db and restore it in production server. Then you can run these t-sql statements for creating the same login and password as that of the test environment.

Code:
Use Master
go
-- Creates a new SQL Server login that allows a user to connect to a 
server running SQL Server using SQL Server Authentication.
EXEC sp_addlogin 'dwayne', 'your_choice', 'YourDB'
go

-- Adds a security account in the current database for a SQL 
Server login or Microsoft Windows NT user or group, and enables it to 
be granted permissions to perform activities in the database.
EXEC sp_grantdbaccess 'dwayne', 'dwayne'
go

Use YourDB
Go
Adds a security account as a member of an existing SQL Server 
database role in the current database.
EXEC sp_addrolemember 'db_owner', 'dwayne'
go
This would add the login and grant db access to that login, and set that login as owner of the db.
You can run these set of statements logged in to sql server as SA account(using Query Analyser tool).
Marked in red-login name, brown-subject to change depending on your db name that you use and blue-password.

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection
 
Old August 17th, 2004, 11:01 AM
Authorized User
 
Join Date: Apr 2004
Posts: 50
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Vijay,

Thanks a bunch! This information was extremely helpful. I really appreciate it.

I should use the backup/restore option instead of the detach/re-attach method when porting the database to the remote production web server?

When I write the stored procedures that create the SQL Server login will those run automatically when the database is restored on the remote web server? Or do those have to be executed manually?

Again, the informaiton was very helpful!
Thanks,
-Dman100-

 
Old August 17th, 2004, 04:10 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Yes Dman, you got to use Backup/Restore options, rather that using Detach/re-attach to take your db to production server.

Also, you got to execute that sp manually, it doesn't run automatically.

Cheers!

_________________________
- Vijay G
Strive for Perfection





Similar Threads
Thread Thread Starter Forum Replies Last Post
Connection string bex BOOK: ASP.NET 2.0 Instant Results ISBN: 978-0-471-74951-6 4 August 3rd, 2008 02:16 PM
connection string nalla ASP.NET 1.0 and 1.1 Professional 2 January 23rd, 2006 04:03 AM
Connection String phungleon Classic ASP Basics 1 March 18th, 2005 05:51 AM
Connection string tlamazares SQL Server ASP 1 March 29th, 2004 05:16 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.