Connecting to DB using Users and Roles

[Ninel]

I am new to DBA related stuff so please bare with me...

I am working for a small company that wants me to create a database for their website which is hosted by a 3rd party company.

In order to write the code I had to install Interdev, SQL Server, and Windows NT option pack for a server.

The computer that I use has username: Administrator and no password (I don't know if this is the same as SA and no password). The company's server is running off my computer.

For now all I really want to do is connect to the database and test the web page.
I know that in connecting to the database I should really use a userid and password.

I would like to use the following connectionstring:

"Provider=SQLOLEDB; Data Source=SERVER; Initial Catalog=MonarchMedical; User ID=monarchuser; Password=monarchuser"

I believe that my domain name is called SERVER. The database I wan to conncet to is MonarchMedical and the userid and password I want to create is monarchuser.

This is where I'm stuck. Does this involve creating users and roles? How do I assign this to my login (Administrator).

I even tried to connect to the Northwind database using no userid and password and it gives me the following error:

Login failed for user 'IUSR_SERVER'. Error in database connection

What am I doing wrong? How can I get this to work?

Please help!!!!

[JBane]

Your connection string is not using a trusted connection, so it is looking for a database login. First logon to your SQL Server through enterprise manager. You can use windows authentication to register the server since you are an admin. Next you'll need to create a login for your website to use. In this example we'll call the login monarchuser with a password of monarchuser like you are doing. Make sure you put the login into the datareader and datawriter roles for the MonarchMedical db. Now alter your connection string to look like this:

"Provider=SQLOLEDB;Data Source=SERVER;Network Library=DBMSSOCN;User ID=monarchuser;Password=monarchuser;Initial Catalog=MonarchMedical;"

And you should be good to go. If you need more detail, please let me know.

Thanks,
Jeff

[Ninel]

How do I create a login for the website?
Is that under security/logins?
I tried doing that...
When the new login window pops up, in the name field I put SERVER\monarchuser.
When I click ok I get error 15401: Windows NT user or group 'SERVER\monarchuser' not found.Check name again.

Am I creating the Login incorrectly?

[JBane]

No, that's how you create them. If you click the elipses button (...) You should see a list of all users available. Does it show up there?

Jeff

[Ninel]

When I click on the drop down list, I have to choose the username that I currently logon to Windows with..correct?
Well I logon using the administrator username with no password. When I click the dropdown list I see 'sa'. I'm assuming that's the administrator username. When I try to select that it also gives me an error.

I want to create 'monarchuser' as the user. monarchuser doesn't exist at this point.

[JBane]

Does it exist as an OS login? It needs to first before it will show up in the list. Also check out the Create Login Wizard off the Tools-->Wizards-->Database menu. This will walk you through the process.

Jeff

[Ninel]

'Monarchuser' is not an OS Login. Does it need to be in order to pass it as userid and password in the connectionstring?
I thought I could just create monarchuser under the login that I use to log in to Windows. I use the Administrator username.

Will this not work with the Administrator username login?

[JBane]

There are two types of logins you can create, integrated and standard. An integrated login must exsist as a windows account and then is added to SQL Server. A standard login is only an SQL Server login. There is a radio button to choose which kind of authentication you want the login to use. You should be able to create a standard login logged in as yourself, then use the connection string like I have showed.

Jeff

[Ninel]

JBane,
I created monarchuser as a new OS login. I did everything you told me to do and ran the connection.
It finally worked.

Thank you so much for all your help.
Ninel

[JBane]

No Problem.

Jeff

[Ninel]

I have one more question if you don't mind.

Will anyone be able to access the database over the internet with this connectionstring that uses monarchuser as a userid and password?

[JBane]

If anonymous access is allowed to the site, yes.

Jeff

[Ninel]

What exactly do you mean anonymous access? And what do I do to allow it?

[JBane]

It's defined at the IIS level. The 3rd party would have to set it up. My guess is it's already turned on.

Jeff