sql2k sp3
Is there a way to attach the files of a db to a newly
created box? No backups as these are QA db's not
production.
TIA, ChrisR"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:c6fe01c48a03$56389420$a401280a@.phx.gbl...
> sql2k sp3
> Is there a way to attach the files of a db to a newly
> created box? No backups as these are QA db's not
> production.
Assuming that these files are now in use by SQL Server there are several
methods. You can detach the database, copy the *.mdf & *.ldf file to the new
server and reattach the files. Or, use the BACKUP command, then RESTORE on
your target server.
The following reference covers your options fairly well:
http://support.microsoft.com/default.aspx?scid=kb;en-us;314546
Steve
Showing posts with label production. Show all posts
Showing posts with label production. Show all posts
Thursday, March 29, 2012
Thursday, March 8, 2012
Assigning a lower priority to some users in SQL Server.
I have a production database used by a web site, and at the same time a group of read-only users who can query the database directly “without the web site”
When one of the users runs a complex query, it slows down the server, and affects the web site.
Is it possible to change the SQL User account or SQL User Group’s priority to low?
You know, the same like in the Task Manager and Windows, I can change a process to low, so it will not affect the important processes, can I do this in SQL Server, and is there any workaround.
in my humble opinion
its the query that needs to be changed
or if your using 2005 you can implement HA feature
such as database snapshot, mirroring etc.
|||Besides the guessing, does anyone have a real solution? Are there sql execution priorities available in SQL Server? Or they are just in the deal databases, like Oracle?|||
if you are so convince that thats the best query you can write and there is no
room for improvement then you can schedule your heavy process to run
on offpeak times.
if that heavy process cross the line of tolerable performance your only option
is to kill that process.
the solution to your problem are
1. send readonly report users to a database snapshot if you are using 2k5
2. schedule the process to run on offpeak times
3. use page caching, fragment caching and most importanctly database caching in asp.net or on your website so you dont rely much on your database
By the way, what does this complex query do? if you would not mind.
how complex is it?
Assign permissions to multiple objects
I know I should know the answer to this question, but unfortunately I
do not.
We have a SQL Server 7.0 production database (will be upgraded soon).
I am attempting to restore this database in my development environment
which is running SQL Server 2005 developer edition. The restore
appeared to succeed without problem, but one of the logins ended up not
getting any of the correct permissions assigned to any objects for some
reason. Is there any way to easily assign permissions to multiple
objects for a single login? If I have to do it manually and
individually it will take hours.
Thanks in advance for any advice.Yes
1. Simply add user to a group with enough perms to do it.
or
2. use sp_MSForeachtable or a cursor and loop through the objects assigning
perms as appriopriate by generating dynamic sql.
or
3. Script out perms from prod and apply to dev for login in question.
"rsbaier@.gmail.com" wrote:
> I know I should know the answer to this question, but unfortunately I
> do not.
> We have a SQL Server 7.0 production database (will be upgraded soon).
> I am attempting to restore this database in my development environment
> which is running SQL Server 2005 developer edition. The restore
> appeared to succeed without problem, but one of the logins ended up not
> getting any of the correct permissions assigned to any objects for some
> reason. Is there any way to easily assign permissions to multiple
> objects for a single login? If I have to do it manually and
> individually it will take hours.
> Thanks in advance for any advice.
>
do not.
We have a SQL Server 7.0 production database (will be upgraded soon).
I am attempting to restore this database in my development environment
which is running SQL Server 2005 developer edition. The restore
appeared to succeed without problem, but one of the logins ended up not
getting any of the correct permissions assigned to any objects for some
reason. Is there any way to easily assign permissions to multiple
objects for a single login? If I have to do it manually and
individually it will take hours.
Thanks in advance for any advice.Yes
1. Simply add user to a group with enough perms to do it.
or
2. use sp_MSForeachtable or a cursor and loop through the objects assigning
perms as appriopriate by generating dynamic sql.
or
3. Script out perms from prod and apply to dev for login in question.
"rsbaier@.gmail.com" wrote:
> I know I should know the answer to this question, but unfortunately I
> do not.
> We have a SQL Server 7.0 production database (will be upgraded soon).
> I am attempting to restore this database in my development environment
> which is running SQL Server 2005 developer edition. The restore
> appeared to succeed without problem, but one of the logins ended up not
> getting any of the correct permissions assigned to any objects for some
> reason. Is there any way to easily assign permissions to multiple
> objects for a single login? If I have to do it manually and
> individually it will take hours.
> Thanks in advance for any advice.
>
Monday, February 13, 2012
asp.net sql connection problem
Hi all,
I have a production environment where I have a 2-node SQL 2005 Cluster
running on boxes which are part of a domain "MyCompany". I have setup a
domain user account called "netasp" and then given it permissions to the
database. I then setup a new App Pool in IIS on my Web Server (not part of
domain, can access SQL server via TCP) with identity setup to a local
account called "netasp" with the same password as the domain user account. I
then setup my website under this App Pool and tried my app. For some reason
it will not connect. This works in a development environment where my web &
sql is on the same box (non cluster environment). How do I go about getting
this to work? For security reasons, my web servers cannot be part of the
domain.
TIA!On Tue, 18 Apr 2006 02:48:39 -0500, <param@.community.nospam> wrote:
Hi all,
I have a production environment where I have a 2-node SQL 2005 Cluster
running on boxes which are part of a domain "MyCompany". I have setup a
domain user account called "netasp" and then given it permissions to the
database. I then setup a new App Pool in IIS on my Web Server (not part of
domain, can access SQL server via TCP) with identity setup to a local
account called "netasp" with the same password as the domain user account.
I
then setup my website under this App Pool and tried my app. For some reaso
n
it will not connect. This works in a development environment where my web
&
sql is on the same box (non cluster environment). How do I go about gettin
g
this to work? For security reasons, my web servers cannot be part of the
domain.
Is your web app and database connection configured for Windows integrated se
curity? If so you
probably have a delegation issue.
Paul
~~~~
Microsoft MVP (Visual Basic)|||My web app actually uses forms authentication by authenticating against the
database. I have heard of cases where people got this to work. I just need
some guidance/help in getting it to work.
TIA!
"Paul Clement" <UseAdddressAtEndofMessage@.swspectrum.com> wrote in message
news:dhl942hp9ifa1gncs1aokpfdqn2s2d1tjp@.
4ax.com...
> On Tue, 18 Apr 2006 02:48:39 -0500, <param@.community.nospam> wrote:
> Hi all,
>
> I have a production environment where I have a 2-node SQL 2005 Cluster
> running on boxes which are part of a domain "MyCompany". I have setup a
> domain user account called "netasp" and then given it permissions to the
> database. I then setup a new App Pool in IIS on my Web Server (not part
> of
> domain, can access SQL server via TCP) with identity setup to a local
> account called "netasp" with the same password as the domain user
> account. I
> then setup my website under this App Pool and tried my app. For some
> reason
> it will not connect. This works in a development environment where my
> web &
> sql is on the same box (non cluster environment). How do I go about
> getting
> this to work? For security reasons, my web servers cannot be part of the
> domain.
>
> Is your web app and database connection configured for Windows integrated
> security? If so you
> probably have a delegation issue.
>
> Paul
> ~~~~
> Microsoft MVP (Visual Basic)|||The problem is that you gave permissions to the domain account, but you
running it as a local account. The remote SQL Server doesn't know about
your local 'netasp', it only knows about the domain one. I'm not sure you
can 'fool' the SQL server in thinking that the local machine account of the
remote web server is the same thing as the domain account it knows about. I
think you'd have to make your web server part of the domain, and run it
under the domain account. That, or just use username/password for the the
sql server authentication instead of integrated.
<param@.community.nospam> wrote in message
news:ODwLDyrYGHA.4652@.TK2MSFTNGP04.phx.gbl...
> Hi all,
> I have a production environment where I have a 2-node SQL 2005 Cluster
> running on boxes which are part of a domain "MyCompany". I have setup a
> domain user account called "netasp" and then given it permissions to the
> database. I then setup a new App Pool in IIS on my Web Server (not part of
> domain, can access SQL server via TCP) with identity setup to a local
> account called "netasp" with the same password as the domain user account.
> I then setup my website under this App Pool and tried my app. For some
> reason it will not connect. This works in a development environment where
> my web & sql is on the same box (non cluster environment). How do I go
> about getting this to work? For security reasons, my web servers cannot be
> part of the domain.
> TIA!
>|||I would love to use SQL Auth, but the beauty of windows auth is I dont have
to specify the username/pwd in the config file. Is there no way at all to
get this to work? Does MS recommend putting your Web Servers in the same
domain as the SQL? Our security consultant told us never to do that. Leave
the Web in the DMZ..
TIA!
"Marina Levit [MVP]" <someone@.nospam.com> wrote in message
news:eOeZGZvYGHA.3848@.TK2MSFTNGP05.phx.gbl...
> The problem is that you gave permissions to the domain account, but you
> running it as a local account. The remote SQL Server doesn't know about
> your local 'netasp', it only knows about the domain one. I'm not sure you
> can 'fool' the SQL server in thinking that the local machine account of
> the remote web server is the same thing as the domain account it knows
> about. I think you'd have to make your web server part of the domain, and
> run it under the domain account. That, or just use username/password for
> the the sql server authentication instead of integrated.
> <param@.community.nospam> wrote in message
> news:ODwLDyrYGHA.4652@.TK2MSFTNGP04.phx.gbl...
>|||I can't say I have complete knowledge of windows security, but I don't know
how to get this to work, although there could be a way.
First off, if someone gets access to your web server, you could argue that
they will get access to the sql server through windows authentication if
they really try anyway - so it will be the same as if they saw the
username/password in the config file.
As far as storing the username/password in the config file (or elsewhere in
a different file, even outside your virtual directory), you could encrypt
the connection information to provide an extra level of security.
<param@.community.nospam> wrote in message
news:uYxROfvYGHA.3532@.TK2MSFTNGP05.phx.gbl...
>I would love to use SQL Auth, but the beauty of windows auth is I dont have
>to specify the username/pwd in the config file. Is there no way at all to
>get this to work? Does MS recommend putting your Web Servers in the same
>domain as the SQL? Our security consultant told us never to do that. Leave
>the Web in the DMZ..
> TIA!
> "Marina Levit [MVP]" <someone@.nospam.com> wrote in message
> news:eOeZGZvYGHA.3848@.TK2MSFTNGP05.phx.gbl...
>|||On Tue, 18 Apr 2006 09:53:06 -0500, <param@.community.nospam> wrote:
I would love to use SQL Auth, but the beauty of windows auth is I dont hav
e
to specify the username/pwd in the config file. Is there no way at all to
get this to work? Does MS recommend putting your Web Servers in the same
domain as the SQL? Our security consultant told us never to do that. Leave
the Web in the DMZ..
See if the following helps:
How To: Use Forms Authentication with Active Directory in Multiple Domains i
n ASP.NET 2.0
http://msdn.microsoft.com/library/d... />
000021.asp
Paul
~~~~
Microsoft MVP (Visual Basic)|||I guess, I need to clarify. I do not wish to use Active Directory in anyway
to authenticate my web application users. They will be authenticated off
tables in SQL Server. The problem I am having is making the Asp.Net Web
Application connect to and authenticate against the SQL Server using a
standard specified Windows account. Is there a way to do it when the SQL
server is in a domain environment and the Web Server are in a Workgroup
environment. I have tried SQL Auth and it works fine.
TIA!
"Paul Clement" <UseAdddressAtEndofMessage@.swspectrum.com> wrote in message
news:rh8a4218grgi70ba3tef4td7n80lmcrus5@.
4ax.com...
> On Tue, 18 Apr 2006 09:53:06 -0500, <param@.community.nospam> wrote:
> I would love to use SQL Auth, but the beauty of windows auth is I dont
> have
> to specify the username/pwd in the config file. Is there no way at all
> to
> get this to work? Does MS recommend putting your Web Servers in the same
> domain as the SQL? Our security consultant told us never to do that.
> Leave
> the Web in the DMZ..
>
> See if the following helps:
> How To: Use Forms Authentication with Active Directory in Multiple Domains
> in ASP.NET 2.0
> http://msdn.microsoft.com/library/d...>
ht000021.asp
>
> Paul
> ~~~~
> Microsoft MVP (Visual Basic)|||<param@.community.nospam> wrote in message
news:uWbzbqxYGHA.1580@.TK2MSFTNGP02.phx.gbl...
>I guess, I need to clarify. I do not wish to use Active Directory in anyway
>to authenticate my web application users. They will be authenticated off
>tables in SQL Server. The problem I am having is making the Asp.Net Web
>Application connect to and authenticate against the SQL Server using a
>standard specified Windows account. Is there a way to do it when the SQL
>server is in a domain environment and the Web Server are in a Workgroup
>environment. I have tried SQL Auth and it works fine.
Right - how do you expect to use Windows Auth if both machines are not in a
domain?
Tom Kaminski IIS MVP
http://www.microsoft.com/windowsser...ty/centers/iis/
http://mvp.support.microsoft.com/
http://www.iistoolshed.com/ - tools, scripts, and utilities for running IIS|||Marina wrote on Tue, 18 Apr 2006 10:42:07 -0400:
> The problem is that you gave permissions to the domain account, but you
> running it as a local account. The remote SQL Server doesn't know about
> your local 'netasp', it only knows about the domain one. I'm not sure you
> can 'fool' the SQL server in thinking that the local machine account of
> the remote web server is the same thing as the domain account it knows
> about. I think you'd have to make your web server part of the domain, and
> run it under the domain account. That, or just use username/password for
> the the sql server authentication instead of integrated.
By using the same login name and password on both machines it should work -
certainly does here where I have an IIS server running sites under local
accounts that match credentials on a SQL server machine and they login fine,
both servers are configured to run in the same workgroup (no domains running
in my DMZ). I am however doing this using ASP and via ODBC and OLE DB
connections, so it could be that something is different in the way ASP.NET
passes credentials around.
Dan
I have a production environment where I have a 2-node SQL 2005 Cluster
running on boxes which are part of a domain "MyCompany". I have setup a
domain user account called "netasp" and then given it permissions to the
database. I then setup a new App Pool in IIS on my Web Server (not part of
domain, can access SQL server via TCP) with identity setup to a local
account called "netasp" with the same password as the domain user account. I
then setup my website under this App Pool and tried my app. For some reason
it will not connect. This works in a development environment where my web &
sql is on the same box (non cluster environment). How do I go about getting
this to work? For security reasons, my web servers cannot be part of the
domain.
TIA!On Tue, 18 Apr 2006 02:48:39 -0500, <param@.community.nospam> wrote:
Hi all,
I have a production environment where I have a 2-node SQL 2005 Cluster
running on boxes which are part of a domain "MyCompany". I have setup a
domain user account called "netasp" and then given it permissions to the
database. I then setup a new App Pool in IIS on my Web Server (not part of
domain, can access SQL server via TCP) with identity setup to a local
account called "netasp" with the same password as the domain user account.
I
then setup my website under this App Pool and tried my app. For some reaso
n
it will not connect. This works in a development environment where my web
&
sql is on the same box (non cluster environment). How do I go about gettin
g
this to work? For security reasons, my web servers cannot be part of the
domain.
Is your web app and database connection configured for Windows integrated se
curity? If so you
probably have a delegation issue.
Paul
~~~~
Microsoft MVP (Visual Basic)|||My web app actually uses forms authentication by authenticating against the
database. I have heard of cases where people got this to work. I just need
some guidance/help in getting it to work.
TIA!
"Paul Clement" <UseAdddressAtEndofMessage@.swspectrum.com> wrote in message
news:dhl942hp9ifa1gncs1aokpfdqn2s2d1tjp@.
4ax.com...
> On Tue, 18 Apr 2006 02:48:39 -0500, <param@.community.nospam> wrote:
> Hi all,
>
> I have a production environment where I have a 2-node SQL 2005 Cluster
> running on boxes which are part of a domain "MyCompany". I have setup a
> domain user account called "netasp" and then given it permissions to the
> database. I then setup a new App Pool in IIS on my Web Server (not part
> of
> domain, can access SQL server via TCP) with identity setup to a local
> account called "netasp" with the same password as the domain user
> account. I
> then setup my website under this App Pool and tried my app. For some
> reason
> it will not connect. This works in a development environment where my
> web &
> sql is on the same box (non cluster environment). How do I go about
> getting
> this to work? For security reasons, my web servers cannot be part of the
> domain.
>
> Is your web app and database connection configured for Windows integrated
> security? If so you
> probably have a delegation issue.
>
> Paul
> ~~~~
> Microsoft MVP (Visual Basic)|||The problem is that you gave permissions to the domain account, but you
running it as a local account. The remote SQL Server doesn't know about
your local 'netasp', it only knows about the domain one. I'm not sure you
can 'fool' the SQL server in thinking that the local machine account of the
remote web server is the same thing as the domain account it knows about. I
think you'd have to make your web server part of the domain, and run it
under the domain account. That, or just use username/password for the the
sql server authentication instead of integrated.
<param@.community.nospam> wrote in message
news:ODwLDyrYGHA.4652@.TK2MSFTNGP04.phx.gbl...
> Hi all,
> I have a production environment where I have a 2-node SQL 2005 Cluster
> running on boxes which are part of a domain "MyCompany". I have setup a
> domain user account called "netasp" and then given it permissions to the
> database. I then setup a new App Pool in IIS on my Web Server (not part of
> domain, can access SQL server via TCP) with identity setup to a local
> account called "netasp" with the same password as the domain user account.
> I then setup my website under this App Pool and tried my app. For some
> reason it will not connect. This works in a development environment where
> my web & sql is on the same box (non cluster environment). How do I go
> about getting this to work? For security reasons, my web servers cannot be
> part of the domain.
> TIA!
>|||I would love to use SQL Auth, but the beauty of windows auth is I dont have
to specify the username/pwd in the config file. Is there no way at all to
get this to work? Does MS recommend putting your Web Servers in the same
domain as the SQL? Our security consultant told us never to do that. Leave
the Web in the DMZ..
TIA!
"Marina Levit [MVP]" <someone@.nospam.com> wrote in message
news:eOeZGZvYGHA.3848@.TK2MSFTNGP05.phx.gbl...
> The problem is that you gave permissions to the domain account, but you
> running it as a local account. The remote SQL Server doesn't know about
> your local 'netasp', it only knows about the domain one. I'm not sure you
> can 'fool' the SQL server in thinking that the local machine account of
> the remote web server is the same thing as the domain account it knows
> about. I think you'd have to make your web server part of the domain, and
> run it under the domain account. That, or just use username/password for
> the the sql server authentication instead of integrated.
> <param@.community.nospam> wrote in message
> news:ODwLDyrYGHA.4652@.TK2MSFTNGP04.phx.gbl...
>|||I can't say I have complete knowledge of windows security, but I don't know
how to get this to work, although there could be a way.
First off, if someone gets access to your web server, you could argue that
they will get access to the sql server through windows authentication if
they really try anyway - so it will be the same as if they saw the
username/password in the config file.
As far as storing the username/password in the config file (or elsewhere in
a different file, even outside your virtual directory), you could encrypt
the connection information to provide an extra level of security.
<param@.community.nospam> wrote in message
news:uYxROfvYGHA.3532@.TK2MSFTNGP05.phx.gbl...
>I would love to use SQL Auth, but the beauty of windows auth is I dont have
>to specify the username/pwd in the config file. Is there no way at all to
>get this to work? Does MS recommend putting your Web Servers in the same
>domain as the SQL? Our security consultant told us never to do that. Leave
>the Web in the DMZ..
> TIA!
> "Marina Levit [MVP]" <someone@.nospam.com> wrote in message
> news:eOeZGZvYGHA.3848@.TK2MSFTNGP05.phx.gbl...
>|||On Tue, 18 Apr 2006 09:53:06 -0500, <param@.community.nospam> wrote:
I would love to use SQL Auth, but the beauty of windows auth is I dont hav
e
to specify the username/pwd in the config file. Is there no way at all to
get this to work? Does MS recommend putting your Web Servers in the same
domain as the SQL? Our security consultant told us never to do that. Leave
the Web in the DMZ..
See if the following helps:
How To: Use Forms Authentication with Active Directory in Multiple Domains i
n ASP.NET 2.0
http://msdn.microsoft.com/library/d... />
000021.asp
Paul
~~~~
Microsoft MVP (Visual Basic)|||I guess, I need to clarify. I do not wish to use Active Directory in anyway
to authenticate my web application users. They will be authenticated off
tables in SQL Server. The problem I am having is making the Asp.Net Web
Application connect to and authenticate against the SQL Server using a
standard specified Windows account. Is there a way to do it when the SQL
server is in a domain environment and the Web Server are in a Workgroup
environment. I have tried SQL Auth and it works fine.
TIA!
"Paul Clement" <UseAdddressAtEndofMessage@.swspectrum.com> wrote in message
news:rh8a4218grgi70ba3tef4td7n80lmcrus5@.
4ax.com...
> On Tue, 18 Apr 2006 09:53:06 -0500, <param@.community.nospam> wrote:
> I would love to use SQL Auth, but the beauty of windows auth is I dont
> have
> to specify the username/pwd in the config file. Is there no way at all
> to
> get this to work? Does MS recommend putting your Web Servers in the same
> domain as the SQL? Our security consultant told us never to do that.
> Leave
> the Web in the DMZ..
>
> See if the following helps:
> How To: Use Forms Authentication with Active Directory in Multiple Domains
> in ASP.NET 2.0
> http://msdn.microsoft.com/library/d...>
ht000021.asp
>
> Paul
> ~~~~
> Microsoft MVP (Visual Basic)|||<param@.community.nospam> wrote in message
news:uWbzbqxYGHA.1580@.TK2MSFTNGP02.phx.gbl...
>I guess, I need to clarify. I do not wish to use Active Directory in anyway
>to authenticate my web application users. They will be authenticated off
>tables in SQL Server. The problem I am having is making the Asp.Net Web
>Application connect to and authenticate against the SQL Server using a
>standard specified Windows account. Is there a way to do it when the SQL
>server is in a domain environment and the Web Server are in a Workgroup
>environment. I have tried SQL Auth and it works fine.
Right - how do you expect to use Windows Auth if both machines are not in a
domain?
Tom Kaminski IIS MVP
http://www.microsoft.com/windowsser...ty/centers/iis/
http://mvp.support.microsoft.com/
http://www.iistoolshed.com/ - tools, scripts, and utilities for running IIS|||Marina wrote on Tue, 18 Apr 2006 10:42:07 -0400:
> The problem is that you gave permissions to the domain account, but you
> running it as a local account. The remote SQL Server doesn't know about
> your local 'netasp', it only knows about the domain one. I'm not sure you
> can 'fool' the SQL server in thinking that the local machine account of
> the remote web server is the same thing as the domain account it knows
> about. I think you'd have to make your web server part of the domain, and
> run it under the domain account. That, or just use username/password for
> the the sql server authentication instead of integrated.
By using the same login name and password on both machines it should work -
certainly does here where I have an IIS server running sites under local
accounts that match credentials on a SQL server machine and they login fine,
both servers are configured to run in the same workgroup (no domains running
in my DMZ). I am however doing this using ASP and via ODBC and OLE DB
connections, so it could be that something is different in the way ASP.NET
passes credentials around.
Dan
Subscribe to:
Posts (Atom)