Set up SQL Trusted Users - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Set up SQL Trusted Users

Author

Topic

Tinkerer
Starting Member

4 Posts

Posted - 2007-03-08 : 12:53:20

Can someone please help me set up a user for sql server.

Background - ZERO sql server experience - only used MS access a bit

I have a SQL database that was created automatically for me by ASP.NET membership - for use with the login function on web sites.

The login runs fine on my pc running sql server express

When I put it on my host (who is using SQL server 2000) it does not work & I have been told that I need to change the connection string to include Server (got that) userid and password.

And there is the problem - how do I add a user with a password to a sql server database on my pc which is then ftp'd out to my internet host so I can use that user ID and password in my connection string?

Now as I say I know nothing about sql - Please baby me if you can help ....

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-03-08 : 12:58:06

You need to use standard security:
http://www.connectionstrings.com/?carrier=sqlserver

This is known as SQL authentication. Trusted is known as Windows authentication.

Tara Kizer

Tinkerer
Starting Member

4 Posts

Posted - 2007-03-08 : 13:48:21

Tara - that clears up one thing already thx

So I went to the site & tried

Provider=sqloledb;Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;

which then means that NT authority/Network Services is the user trying to access the DB but I don't know how to give that permission

I have an atricle that walks through using sp_grantlogin, sp_grantdbaccess and sp_addrolemember...

is this what I should be doing to setup the database?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-03-08 : 14:03:04

You can't grant NT authority/Network Services access to the database as that isn't a real account. The account that is used for Integrated Security is the account that is currently logged in or the account that the service is running (if the app is running from a service).

But yes those are the system stored procedures that you would use. You can also just use Enterprise Manager (2000) or Management Studio (2005) to grant the access. Those tools would be a little easier for a newbie as you can grant the access through a GUI.

Tara Kizer

Tinkerer
Starting Member

4 Posts

Posted - 2007-03-08 : 14:32:52

So I set up another user...

So on my pc...
Server--> Security--> Logins --> create new user & called it user1

selected sql server authentication and entered a password

Then for that user I selected User mappings and selected the database for my web application aspnetdb.mdf and selected aspnet_membership_FullAccess for the database roles

on status it is set to grant and enabled

I copied the database up to the sql 200 server

I then edited the connection string to use the user1 id with the password I set up.

Result = login failed for user 'user1'

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-03-08 : 14:37:50

Copying the database to the SQL Server 2000 server does not copy the login to the server. You must do that on the server as well.

Tara Kizer

Tinkerer
Starting Member

4 Posts

Posted - 2007-03-08 : 14:46:30

Tara - does that mean that the only way I can do it is by asking my web hosting company to set up a login & password for me?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-03-08 : 15:00:11

Logins are stored in the master database, which you probably don't have access to. So yes you will need to contact them.

Tara Kizer