Wednesday, March 21, 2012

How to setup a SQL Database for a shared web hosting?

I would like to offer SQL Database with my shared hosting and don't know how
to do this. Please note that both SQL and IIS are in the same machine. I
appriciate any help on this.
Thanks,
MikeIn a nutshell, you create a blank database for each customer, create a user
account that has dbo rights to that database (and no rights to any other),
and for goodness sake...make sure you back it up for them.
By the way...limit the number of databases, keep an eye on the usage and
performance of the server, and please make sure you call Microsoft to ensure
you have the proper licensing fees paid. Failure to do this opens you up to
legal liabililty.
Having IIS and SQL on the same box and doing shared hosting is a really bad
idea. Are you sure you are ready to go into SQL Server hosting?
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Mike R." <MikeR@.discussions.microsoft.com> wrote in message
news:90A6A1EC-E3F6-4268-B938-5CAC626030D2@.microsoft.com...
>I would like to offer SQL Database with my shared hosting and don't know
>how
> to do this. Please note that both SQL and IIS are in the same machine. I
> appriciate any help on this.
> Thanks,
> Mike|||Thanks Kevin for the quick reply.
I already had a user to give the rights to for this particular database.
So after reading your post I went into SQL Server Enterprise Manager and
created an empty database for that user. Then under Users of that database I
added the user from the Active Directory. I gave this user public and
db_owner role.
I tried to connect to this database remotely and I'm getting below error:
Connection failed:
SQLState: '01000'
SQL Server Error: 53
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).
Connection failed:
SQLState: '08001'
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or
access deined.
Can you please advice me? Thank you very much.
"Kevin3NF" wrote:
> In a nutshell, you create a blank database for each customer, create a user
> account that has dbo rights to that database (and no rights to any other),
> and for goodness sake...make sure you back it up for them.
> By the way...limit the number of databases, keep an eye on the usage and
> performance of the server, and please make sure you call Microsoft to ensure
> you have the proper licensing fees paid. Failure to do this opens you up to
> legal liabililty.
> Having IIS and SQL on the same box and doing shared hosting is a really bad
> idea. Are you sure you are ready to go into SQL Server hosting?
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
> www.experts-exchange.com - experts compete for points to answer your
> questions
>
> "Mike R." <MikeR@.discussions.microsoft.com> wrote in message
> news:90A6A1EC-E3F6-4268-B938-5CAC626030D2@.microsoft.com...
> >I would like to offer SQL Database with my shared hosting and don't know
> >how
> > to do this. Please note that both SQL and IIS are in the same machine. I
> > appriciate any help on this.
> >
> > Thanks,
> >
> > Mike
>
>|||First off, if you are a web host, you would be using SQL Server logins, not
Active Directory.
Remote connections more than likely need to be done via IP address, and the
correct port opened through the firewall.
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Mike R." <MikeR@.discussions.microsoft.com> wrote in message
news:09F06CF0-4A01-4B7C-97A0-902667EAE649@.microsoft.com...
> Thanks Kevin for the quick reply.
> I already had a user to give the rights to for this particular database.
> So after reading your post I went into SQL Server Enterprise Manager and
> created an empty database for that user. Then under Users of that database
> I
> added the user from the Active Directory. I gave this user public and
> db_owner role.
> I tried to connect to this database remotely and I'm getting below error:
> Connection failed:
> SQLState: '01000'
> SQL Server Error: 53
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).
> Connection failed:
> SQLState: '08001'
> SQL Server Error: 17
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or
> access deined.
> Can you please advice me? Thank you very much.
>
> "Kevin3NF" wrote:
>> In a nutshell, you create a blank database for each customer, create a
>> user
>> account that has dbo rights to that database (and no rights to any
>> other),
>> and for goodness sake...make sure you back it up for them.
>> By the way...limit the number of databases, keep an eye on the usage and
>> performance of the server, and please make sure you call Microsoft to
>> ensure
>> you have the proper licensing fees paid. Failure to do this opens you up
>> to
>> legal liabililty.
>> Having IIS and SQL on the same box and doing shared hosting is a really
>> bad
>> idea. Are you sure you are ready to go into SQL Server hosting?
>> --
>> Kevin Hill
>> President
>> 3NF Consulting
>> www.3nf-inc.com/NewsGroups.htm
>> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
>> www.experts-exchange.com - experts compete for points to answer your
>> questions
>>
>> "Mike R." <MikeR@.discussions.microsoft.com> wrote in message
>> news:90A6A1EC-E3F6-4268-B938-5CAC626030D2@.microsoft.com...
>> >I would like to offer SQL Database with my shared hosting and don't know
>> >how
>> > to do this. Please note that both SQL and IIS are in the same machine.
>> > I
>> > appriciate any help on this.
>> >
>> > Thanks,
>> >
>> > Mike
>>|||Thank you very much. I created a SQL user and opened the firewall port and
it's working now :)
"Kevin3NF" wrote:
> First off, if you are a web host, you would be using SQL Server logins, not
> Active Directory.
> Remote connections more than likely need to be done via IP address, and the
> correct port opened through the firewall.
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
> www.experts-exchange.com - experts compete for points to answer your
> questions
>
> "Mike R." <MikeR@.discussions.microsoft.com> wrote in message
> news:09F06CF0-4A01-4B7C-97A0-902667EAE649@.microsoft.com...
> > Thanks Kevin for the quick reply.
> >
> > I already had a user to give the rights to for this particular database.
> >
> > So after reading your post I went into SQL Server Enterprise Manager and
> > created an empty database for that user. Then under Users of that database
> > I
> > added the user from the Active Directory. I gave this user public and
> > db_owner role.
> >
> > I tried to connect to this database remotely and I'm getting below error:
> >
> > Connection failed:
> > SQLState: '01000'
> > SQL Server Error: 53
> > [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).
> > Connection failed:
> > SQLState: '08001'
> > SQL Server Error: 17
> > [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or
> > access deined.
> >
> > Can you please advice me? Thank you very much.
> >
> >
> > "Kevin3NF" wrote:
> >
> >> In a nutshell, you create a blank database for each customer, create a
> >> user
> >> account that has dbo rights to that database (and no rights to any
> >> other),
> >> and for goodness sake...make sure you back it up for them.
> >>
> >> By the way...limit the number of databases, keep an eye on the usage and
> >> performance of the server, and please make sure you call Microsoft to
> >> ensure
> >> you have the proper licensing fees paid. Failure to do this opens you up
> >> to
> >> legal liabililty.
> >>
> >> Having IIS and SQL on the same box and doing shared hosting is a really
> >> bad
> >> idea. Are you sure you are ready to go into SQL Server hosting?
> >>
> >> --
> >> Kevin Hill
> >> President
> >> 3NF Consulting
> >>
> >> www.3nf-inc.com/NewsGroups.htm
> >>
> >> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
> >>
> >> www.experts-exchange.com - experts compete for points to answer your
> >> questions
> >>
> >>
> >> "Mike R." <MikeR@.discussions.microsoft.com> wrote in message
> >> news:90A6A1EC-E3F6-4268-B938-5CAC626030D2@.microsoft.com...
> >> >I would like to offer SQL Database with my shared hosting and don't know
> >> >how
> >> > to do this. Please note that both SQL and IIS are in the same machine.
> >> > I
> >> > appriciate any help on this.
> >> >
> >> > Thanks,
> >> >
> >> > Mike
> >>
> >>
> >>
>
>|||For security reasons, you may want to consider changing the default port
that SQL Server listens on to something other than 1433, since every script
kiddie on the planet knows that one...
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Mike R." <MikeR@.discussions.microsoft.com> wrote in message
news:DED2207A-8934-485D-A944-67D7D8C43557@.microsoft.com...
> Thank you very much. I created a SQL user and opened the firewall port and
> it's working now :)
> "Kevin3NF" wrote:
>> First off, if you are a web host, you would be using SQL Server logins,
>> not
>> Active Directory.
>> Remote connections more than likely need to be done via IP address, and
>> the
>> correct port opened through the firewall.
>> --
>> Kevin Hill
>> President
>> 3NF Consulting
>> www.3nf-inc.com/NewsGroups.htm
>> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
>> www.experts-exchange.com - experts compete for points to answer your
>> questions
>>
>> "Mike R." <MikeR@.discussions.microsoft.com> wrote in message
>> news:09F06CF0-4A01-4B7C-97A0-902667EAE649@.microsoft.com...
>> > Thanks Kevin for the quick reply.
>> >
>> > I already had a user to give the rights to for this particular
>> > database.
>> >
>> > So after reading your post I went into SQL Server Enterprise Manager
>> > and
>> > created an empty database for that user. Then under Users of that
>> > database
>> > I
>> > added the user from the Active Directory. I gave this user public and
>> > db_owner role.
>> >
>> > I tried to connect to this database remotely and I'm getting below
>> > error:
>> >
>> > Connection failed:
>> > SQLState: '01000'
>> > SQL Server Error: 53
>> > [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen
>> > (Connect()).
>> > Connection failed:
>> > SQLState: '08001'
>> > SQL Server Error: 17
>> > [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist
>> > or
>> > access deined.
>> >
>> > Can you please advice me? Thank you very much.
>> >
>> >
>> > "Kevin3NF" wrote:
>> >
>> >> In a nutshell, you create a blank database for each customer, create a
>> >> user
>> >> account that has dbo rights to that database (and no rights to any
>> >> other),
>> >> and for goodness sake...make sure you back it up for them.
>> >>
>> >> By the way...limit the number of databases, keep an eye on the usage
>> >> and
>> >> performance of the server, and please make sure you call Microsoft to
>> >> ensure
>> >> you have the proper licensing fees paid. Failure to do this opens you
>> >> up
>> >> to
>> >> legal liabililty.
>> >>
>> >> Having IIS and SQL on the same box and doing shared hosting is a
>> >> really
>> >> bad
>> >> idea. Are you sure you are ready to go into SQL Server hosting?
>> >>
>> >> --
>> >> Kevin Hill
>> >> President
>> >> 3NF Consulting
>> >>
>> >> www.3nf-inc.com/NewsGroups.htm
>> >>
>> >> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area
>> >> DBAs.
>> >>
>> >> www.experts-exchange.com - experts compete for points to answer your
>> >> questions
>> >>
>> >>
>> >> "Mike R." <MikeR@.discussions.microsoft.com> wrote in message
>> >> news:90A6A1EC-E3F6-4268-B938-5CAC626030D2@.microsoft.com...
>> >> >I would like to offer SQL Database with my shared hosting and don't
>> >> >know
>> >> >how
>> >> > to do this. Please note that both SQL and IIS are in the same
>> >> > machine.
>> >> > I
>> >> > appriciate any help on this.
>> >> >
>> >> > Thanks,
>> >> >
>> >> > Mike
>> >>
>> >>
>> >>
>>|||Thanks but how can I change that port on my server?
"Kevin3NF" wrote:
> For security reasons, you may want to consider changing the default port
> that SQL Server listens on to something other than 1433, since every script
> kiddie on the planet knows that one...
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
> www.experts-exchange.com - experts compete for points to answer your
> questions
>
> "Mike R." <MikeR@.discussions.microsoft.com> wrote in message
> news:DED2207A-8934-485D-A944-67D7D8C43557@.microsoft.com...
> > Thank you very much. I created a SQL user and opened the firewall port and
> > it's working now :)
> >
> > "Kevin3NF" wrote:
> >
> >> First off, if you are a web host, you would be using SQL Server logins,
> >> not
> >> Active Directory.
> >>
> >> Remote connections more than likely need to be done via IP address, and
> >> the
> >> correct port opened through the firewall.
> >>
> >> --
> >> Kevin Hill
> >> President
> >> 3NF Consulting
> >>
> >> www.3nf-inc.com/NewsGroups.htm
> >>
> >> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
> >>
> >> www.experts-exchange.com - experts compete for points to answer your
> >> questions
> >>
> >>
> >> "Mike R." <MikeR@.discussions.microsoft.com> wrote in message
> >> news:09F06CF0-4A01-4B7C-97A0-902667EAE649@.microsoft.com...
> >> > Thanks Kevin for the quick reply.
> >> >
> >> > I already had a user to give the rights to for this particular
> >> > database.
> >> >
> >> > So after reading your post I went into SQL Server Enterprise Manager
> >> > and
> >> > created an empty database for that user. Then under Users of that
> >> > database
> >> > I
> >> > added the user from the Active Directory. I gave this user public and
> >> > db_owner role.
> >> >
> >> > I tried to connect to this database remotely and I'm getting below
> >> > error:
> >> >
> >> > Connection failed:
> >> > SQLState: '01000'
> >> > SQL Server Error: 53
> >> > [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen
> >> > (Connect()).
> >> > Connection failed:
> >> > SQLState: '08001'
> >> > SQL Server Error: 17
> >> > [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist
> >> > or
> >> > access deined.
> >> >
> >> > Can you please advice me? Thank you very much.
> >> >
> >> >
> >> > "Kevin3NF" wrote:
> >> >
> >> >> In a nutshell, you create a blank database for each customer, create a
> >> >> user
> >> >> account that has dbo rights to that database (and no rights to any
> >> >> other),
> >> >> and for goodness sake...make sure you back it up for them.
> >> >>
> >> >> By the way...limit the number of databases, keep an eye on the usage
> >> >> and
> >> >> performance of the server, and please make sure you call Microsoft to
> >> >> ensure
> >> >> you have the proper licensing fees paid. Failure to do this opens you
> >> >> up
> >> >> to
> >> >> legal liabililty.
> >> >>
> >> >> Having IIS and SQL on the same box and doing shared hosting is a
> >> >> really
> >> >> bad
> >> >> idea. Are you sure you are ready to go into SQL Server hosting?
> >> >>
> >> >> --
> >> >> Kevin Hill
> >> >> President
> >> >> 3NF Consulting
> >> >>
> >> >> www.3nf-inc.com/NewsGroups.htm
> >> >>
> >> >> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area
> >> >> DBAs.
> >> >>
> >> >> www.experts-exchange.com - experts compete for points to answer your
> >> >> questions
> >> >>
> >> >>
> >> >> "Mike R." <MikeR@.discussions.microsoft.com> wrote in message
> >> >> news:90A6A1EC-E3F6-4268-B938-5CAC626030D2@.microsoft.com...
> >> >> >I would like to offer SQL Database with my shared hosting and don't
> >> >> >know
> >> >> >how
> >> >> > to do this. Please note that both SQL and IIS are in the same
> >> >> > machine.
> >> >> > I
> >> >> > appriciate any help on this.
> >> >> >
> >> >> > Thanks,
> >> >> >
> >> >> > Mike
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||Look at the server network utility, tcp parameters. New port number takes
effect when you restart the SQL Server service
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Mike R." <MikeR@.discussions.microsoft.com> wrote in message
news:4C7E4EA5-E9AA-4C22-BE8C-96FE2DDE67A2@.microsoft.com...
> Thanks but how can I change that port on my server?
> "Kevin3NF" wrote:
>> For security reasons, you may want to consider changing the default port
>> that SQL Server listens on to something other than 1433, since every
>> script
>> kiddie on the planet knows that one...
>> --
>> Kevin Hill
>> President
>> 3NF Consulting
>> www.3nf-inc.com/NewsGroups.htm
>> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
>> www.experts-exchange.com - experts compete for points to answer your
>> questions
>>
>> "Mike R." <MikeR@.discussions.microsoft.com> wrote in message
>> news:DED2207A-8934-485D-A944-67D7D8C43557@.microsoft.com...
>> > Thank you very much. I created a SQL user and opened the firewall port
>> > and
>> > it's working now :)
>> >
>> > "Kevin3NF" wrote:
>> >
>> >> First off, if you are a web host, you would be using SQL Server
>> >> logins,
>> >> not
>> >> Active Directory.
>> >>
>> >> Remote connections more than likely need to be done via IP address,
>> >> and
>> >> the
>> >> correct port opened through the firewall.
>> >>
>> >> --
>> >> Kevin Hill
>> >> President
>> >> 3NF Consulting
>> >>
>> >> www.3nf-inc.com/NewsGroups.htm
>> >>
>> >> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area
>> >> DBAs.
>> >>
>> >> www.experts-exchange.com - experts compete for points to answer your
>> >> questions
>> >>
>> >>
>> >> "Mike R." <MikeR@.discussions.microsoft.com> wrote in message
>> >> news:09F06CF0-4A01-4B7C-97A0-902667EAE649@.microsoft.com...
>> >> > Thanks Kevin for the quick reply.
>> >> >
>> >> > I already had a user to give the rights to for this particular
>> >> > database.
>> >> >
>> >> > So after reading your post I went into SQL Server Enterprise Manager
>> >> > and
>> >> > created an empty database for that user. Then under Users of that
>> >> > database
>> >> > I
>> >> > added the user from the Active Directory. I gave this user public
>> >> > and
>> >> > db_owner role.
>> >> >
>> >> > I tried to connect to this database remotely and I'm getting below
>> >> > error:
>> >> >
>> >> > Connection failed:
>> >> > SQLState: '01000'
>> >> > SQL Server Error: 53
>> >> > [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen
>> >> > (Connect()).
>> >> > Connection failed:
>> >> > SQLState: '08001'
>> >> > SQL Server Error: 17
>> >> > [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not
>> >> > exist
>> >> > or
>> >> > access deined.
>> >> >
>> >> > Can you please advice me? Thank you very much.
>> >> >
>> >> >
>> >> > "Kevin3NF" wrote:
>> >> >
>> >> >> In a nutshell, you create a blank database for each customer,
>> >> >> create a
>> >> >> user
>> >> >> account that has dbo rights to that database (and no rights to any
>> >> >> other),
>> >> >> and for goodness sake...make sure you back it up for them.
>> >> >>
>> >> >> By the way...limit the number of databases, keep an eye on the
>> >> >> usage
>> >> >> and
>> >> >> performance of the server, and please make sure you call Microsoft
>> >> >> to
>> >> >> ensure
>> >> >> you have the proper licensing fees paid. Failure to do this opens
>> >> >> you
>> >> >> up
>> >> >> to
>> >> >> legal liabililty.
>> >> >>
>> >> >> Having IIS and SQL on the same box and doing shared hosting is a
>> >> >> really
>> >> >> bad
>> >> >> idea. Are you sure you are ready to go into SQL Server hosting?
>> >> >>
>> >> >> --
>> >> >> Kevin Hill
>> >> >> President
>> >> >> 3NF Consulting
>> >> >>
>> >> >> www.3nf-inc.com/NewsGroups.htm
>> >> >>
>> >> >> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area
>> >> >> DBAs.
>> >> >>
>> >> >> www.experts-exchange.com - experts compete for points to answer
>> >> >> your
>> >> >> questions
>> >> >>
>> >> >>
>> >> >> "Mike R." <MikeR@.discussions.microsoft.com> wrote in message
>> >> >> news:90A6A1EC-E3F6-4268-B938-5CAC626030D2@.microsoft.com...
>> >> >> >I would like to offer SQL Database with my shared hosting and
>> >> >> >don't
>> >> >> >know
>> >> >> >how
>> >> >> > to do this. Please note that both SQL and IIS are in the same
>> >> >> > machine.
>> >> >> > I
>> >> >> > appriciate any help on this.
>> >> >> >
>> >> >> > Thanks,
>> >> >> >
>> >> >> > Mike
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>

No comments:

Post a Comment