HOw to Saving data from SQL server

Hi

I have only read ( select access) in a sql server database(2000). I am able to scripts the database.

Can any ony have any idea how to extract the data from database?

not the backup of db, i don't have the access.

No DTS to extract data into text file.

some thing like save the table with data?

I have more than 300 tables.

Thanks

sandipan

I think you don't have many options here. If you only have select permission; the only option I see is to export the data via Select...from... You could script a create table and then load it into a new DB where you, perhaps have rights to create a backup.

You could do all that using SSIS.

sql

how to save or retrieve a picture from a table in sql server 2000

hi ,
am wondering how to save or retrieve an image in sql server 2000 table
using vb application or access thx
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!Hi
http://support.microsoft.com/defaul...kb;en-us;153238
http://support.microsoft.com/defaul...kb;en-us;194975
Regards
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Joe Saliba" <josephs73@.hotmail.com> wrote in message
news:OWE7fj3HFHA.3472@.TK2MSFTNGP09.phx.gbl...
> hi ,
> am wondering how to save or retrieve an image in sql server 2000 table
> using vb application or access thx
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!|||You just "select " the colname from the table the same way you would any
other column data. On the client side, you need t otreat the output as a
byte array, and "eed" it to whatever it is being used in ... The techniques
for that part are dependant on what and how it is being used, and the
technology you are doing it in (VB6, VB/C#.Net, ASP, HTML, etc. etc. )
"Joe Saliba" wrote:

> hi ,
> am wondering how to save or retrieve an image in sql server 2000 table
> using vb application or access thx
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!
>

how to save a rtf file into a field

Can you show me how to save a rtf file into a field in the SQL Server 2K
table? In Access, I am able to do that by assigning the data type OLE Object
and I go to Insert -> Oject and browse to the file I want to store in the
field. But I can not do that way in SQL Server. I guess I would declare the
SQL server data type Image. However, I am stuck on how to store an object in
this field.
Also, assume I can store such an object like that (i.e., a rtf file), can I
declare a record set and manupulate that field to my way?
Thank you for your help in advance.you have to convert to and from a byte[]
This will help
http://www.eggheadcafe.com/PrintSea...asp?LINKID=799
Robbe Morris - 2004/2005 Microsoft MVP C#
http://robbemorris.blogspot.com
"Tim" <Tim@.discussions.microsoft.com> wrote in message
news:E3B6B2BA-5B31-4A4C-95DF-C7250E7BE49A@.microsoft.com...
> Can you show me how to save a rtf file into a field in the SQL Server 2K
> table? In Access, I am able to do that by assigning the data type OLE
> Object
> and I go to Insert -> Oject and browse to the file I want to store in the
> field. But I can not do that way in SQL Server. I guess I would declare
> the
> SQL server data type Image. However, I am stuck on how to store an object
> in
> this field.
> Also, assume I can store such an object like that (i.e., a rtf file), can
> I
> declare a record set and manupulate that field to my way?
> Thank you for your help in advance.|||Hi Robbe:
Thank you for your response. Can you please tell me more about this? I am a
newbie and I don't understand what you mean, i.e., What is the byte array?
Also, I am familiar with VB. Can I use VB for this issue?
Thank you for your help.
"Robbe Morris [C# MVP]" wrote:

> you have to convert to and from a byte[]
> This will help
> http://www.eggheadcafe.com/PrintSea...asp?LINKID=799
> --
> Robbe Morris - 2004/2005 Microsoft MVP C#
> http://robbemorris.blogspot.com
>
>
> "Tim" <Tim@.discussions.microsoft.com> wrote in message
> news:E3B6B2BA-5B31-4A4C-95DF-C7250E7BE49A@.microsoft.com...
>
>sql

how to run ssis in sql 2005

I have installed SQL 2005 Express edition both advanced features and toolkit. I have to import a sql 2000 database to sql 2005. I cannot see access to ssis in either Business Intelligence development studio or sql server management studio express. Can you tell me how access and run ssis?? Integration Services isn't included with any edition of SQL Server 2005 Express, as far as I am aware. See http://msdn.microsoft.com/vstudio/express/sql/compare/default.aspx for further information. See the Integration and Interoperability section of the following URL, http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx, for more specific information. Andrew Watt [MVP] <vhrao@.discussions.microsoft.com> wrote in message news:fc88e4bb-cb03-4ba7-8d14-e19bd3a2fd39@.discussions.microsoft.com...I have installed SQL 2005 Express edition both advanced features and toolkit. I have to import a sql 2000 database to sql 2005. I cannot see access to ssis in either Business Intelligence development studio or sql server management studio express. Can you tell me how access and run ssis?|||SSIS is not part of SQL Express, it is included in higher editions.

How to run SQL-Server on a Webserver!

Hi!

Today I decided to finally install MS SQL 2005 Express on my VPS. For a long time I tried to work with MS Access what definitely brings a lot of complications.

I already installed SQL05Exp on my local machine and everything works great here. Because I'm quite new to these things I simply installed SQL05Exp on the Server the same way like locally. I just clicked through the installation with the predefined settings and everything installed without troubles.

When I uploaded my first Application and tried to run it, I got the following error-message:

Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path. Please make sure the user has a local user profile on the computer. The connection will be closed.

I then read somewhere that I could change the web.config connectionstring to "User Instance=False" what leads to the following:

CREATE DATABASE permission denied in database 'master'.
An attempt to attach an auto-named database for file C:\Inetpub\vhosts\gsp-peru.com\subdomains\muestra\httpdocs\App_Data\Database.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

I read a lot of documents about how to install, etc. but it couldn't really use it for me, surely for a lake of basics. When I understand the problem correctly, the user running the app on the server and trying to attach the mdf-database to the sql-server has not the rights to do that. However I don't know what to do against that.

What I would really love is a simple solution that allows me to connect to the sql-server from whatever domain on the server, just by copying the mdf. It also would be nice to use some kind of password in the connectionstring to assure that just my apps can connect to my databases.

I would be very thankful if someone could give me some good basic explanation what to do and maybe also give me a reference to some nice and understandable informations on this.

Best regards,

Markus

In most cases, the error is exactly what the error message says, but most people ignore what it's saying, so I'll break it down:

Error Description
An attempt to attach an auto-named database for file C:\Inetpub\vhosts\gsp-peru.com\subdomains\muestra\httpdocs\App_Data\Database.mdf failed.

Possible causes
1)database with the same name exists
2)specified file cannot be opened,
3)or it is located on UNC share

Fixing #1 Seeing as the you named the mdf "Database", it's quite possible that you have a database called "database" already. Rename the file to something meaningful, like...gsp-peru.mdf

Fixing #2 Who is your SQL Server running as, and do they have read/write permissions to the app_data folder?

Fixing #3 Move the file, heh. This is rarely the cause, as most people don't do this.

|||

Hi Motley, thanks for your help.

Fixing #1 Seeing as the you named the mdf "Database", it's quite possible that you have a database called "database" already. Rename the file to something meaningful, like...gsp-peru.mdf

There is not even one database installed until now, however I tried to change the name but everything is the same.

Fixing #2 Who is your SQL Server running as, and do they have read/write permissions to the app_data folder?

It was running as Network-Service, I also tried as Local Service and as Local System, always the same result.

Fixing #3 Move the file, heh. This is rarely the cause, as most people don't do this.

It is located within the App_Data folder, everything is on the same machine.

What else could it be?

Markus

|||

Did you check the second half of Fix#2? Most likely you need to make sure the user(Network Service) has write/read permissions to the folder (or to the database directly, if you assign permission database by database).

If you use window authentication, you may need assign window login user with write/read permission to this folder too.

|||

Now I tried to assign the user everyone with all possible rights to both the master database located in the installation folder of sql server and I did the same on both databases of my app.

Everything stays the same. What else could cause this errors?

Markus

|||

I didn't mean the user everyone. Try this one (yourMachinesName\theLoginUser) and Network Service (win2k3 IIS 6.0).

to App_Data with write/read permissions.

Here is a link you can check the basics.

Tell you the truth, I went through this one alittle while ago. What I can remember is the security settings. Since you mention this problem, I am curious that how did you access your application? through VWD on the server or through a virtual directory under IIS 6.0? It may help us to understand why the problem didn't go away.

If you are using IIS to access your application, there are some other settings to adjust like Application should run on asp.NET 2.0 , connction string in your web.config modification ...

Hope we can get the application run soon.

|||

Dear limno, thanks for your help.

Yesterday I assigned the user everyone because what I know this is like setting access for all the existing users. It is a quick way to test if it has something to do with rights, after that I can search for the right user which to give access to.

However, I did exactly like you told me, I gave the network service user and the user administrator which i us to log on full rights but the problem stays the same.

Not sure if I understand your second question. Locally I'm using VWD with SQL Express and it works fine. But on my VPS it's not working. Means if I call my website through the browser I get this error-message.

Markus

|||Change the anonymous user accout for the application to you. You can't log in as any of the accounts you mentioned, so they won't have a user directory (C:\Documents and Settings\{User Name}).|||

The Anonymous user of the application is gsp-peru and it also has a user directory. I tried to change it to administrator which changed nothing. Hope I understood your idea.

Markus

|||

If there are any more hints or ideas I would be happy to hear about them.

Limno, you wrote above about a link which seems that you have not set, maybe you could post it again.

Markus

|||

Hello:

Here is the link from the tutorial.

http://www.asp.net/QuickStart/aspnet/doc/data/default.aspx

Please read the Deploy your local database section.

You mentioned you had problem to access the site from the browser.

Which one IIS or the built-in web browser? what is your url looks like?

Also, I think the user we are talking about isyourmachinename\ausername.

If you cannot figure it out, I will start over on my machine to figure it out.

Good luck(sorry for the delay, I was locked out for a while on this site.)

|||

This thread has an answer.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=125227&SiteID=1

|||

Hi limno,

Many thanks for all your replies. I finally got it running and the problem was where we searched. In IIS I chose the application-pool of my domain and assigned as identity "local system". After that I also had to change sql-server to run under local system. Now it is working, however I'm not quite sure if I will use that because of Security reasons. If I understand correctly, it now would be possible to get access to the whole file-system through script and probably more. Beside all users/domains will be able to access all databases within the instance of sql server.

So the next big step would be to get sql server running under the standard IWPD user of the domain and also to include some username/password security through the connection-string.

Is that possible?

Markus

|||

Hello:

FYI:

<connectionStrings>

<add name="conString1" connectionString="Data Source=yourServer;Integrated Security=false;Initial Catalog=YourDB;User ID=yourDBUser;Password=thePassword" providerName="System.Data.SqlClient" />

<add name="conString2" connectionString="Data Source=yourServer;Initial Catalog=yourDB;Integrated Security=True" providerName="System.Data.SqlClient" />
<remove name="LocalSqlServer" />

</connectionStrings>

<roleManager enabled="true"
defaultProvider="CustomizedRoleProvider">
<providers>
<clear />
<add name="CustomizedRoleProvider"
type="System.Web.Security.SqlRoleProvider"
connectionStringName="conString1"
applicationName="/"
/>
</providers>
</roleManager>

<membership defaultProvider="CustomizedMembershipProvider">
<providers>
<clear />
<add name="CustomizedMembershipProvider"
type="System.Web.Security.SqlMembershipProvider"
connectionStringName="conString2"
applicationName="/"
requiresUniqueEmail="false"
minRequiredPasswordLength="1"
minRequiredNonalphanumericCharacters="0"
passwordFormat="Hashed"
maxInvalidPasswordAttempts="5"

/>
</providers>
</membership>

you need create your database user in your own database first.

Assume you merged the ASPNETDB.MDF into yourDB and you are using customized prociders which point back to the default ones for membership and roles.

The applicationName is important. You can type your application name in it. You can search for explainations.

Hope this can save you a little bit time.

How to run queries in SQL Server using Access files

Hello All,
I am new to SQL Server. I have an Access database and I want o run SQL
queries in SQL Server. I have alredy run SQL queries using this database in
Access but cant do it in SQL Server. Can someone advise?
Neeraj
Yes of course. You have to setup the access database as a linked server or
have to use the OPENROWSET or OPENDATASOURCE. I like working with linked
server if you use they permanently. You can access them via the four-part
name schema naming the object as below:
Select * from LinkedServername...ObjectName
More on this is on the BOL with some good examples, makes sure that the path
you define in the settings of the Access database is serverbased. so the
access db has to be reached from the server to use it.
Sample:
USE master
GO
-- To use named parameters:
EXEC sp_addlinkedserver
@.server = 'SEATTLE Mktg',
@.provider = 'Microsoft.Jet.OLEDB.4.0',
@.srvproduct = 'OLE DB Provider for Jet',
@.datasrc = 'C:\MSOffice\Access\Samples\Northwind.mdb'
GO
-- OR to use no named parameters:
USE master
GO
EXEC sp_addlinkedserver
'SEATTLE Mktg',
'OLE DB Provider for Jet',
'Microsoft.Jet.OLEDB.4.0',
'C:\MSOffice\Access\Samples\Northwind.mdb'
GO
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Neeraj" <Neeraj@.discussions.microsoft.com> schrieb im Newsbeitrag
news:FE0A89AE-C691-4D46-A2F5-CA38A7D161F8@.microsoft.com...
> Hello All,
> I am new to SQL Server. I have an Access database and I want o run SQL
> queries in SQL Server. I have alredy run SQL queries using this database
> in
> Access but cant do it in SQL Server. Can someone advise?
> --
> Neeraj

How to run queries in SQL Server using Access files

Hello All,
I am new to SQL Server. I have an Access database and I want o run SQL
queries in SQL Server. I have alredy run SQL queries using this database in
Access but cant do it in SQL Server. Can someone advise?
--
NeerajYes of course. You have to setup the access database as a linked server or
have to use the OPENROWSET or OPENDATASOURCE. I like working with linked
server if you use they permanently. You can access them via the four-part
name schema naming the object as below:
Select * from LinkedServername...ObjectName
More on this is on the BOL with some good examples, makes sure that the path
you define in the settings of the Access database is serverbased. so the
access db has to be reached from the server to use it.
Sample:
USE master
GO
-- To use named parameters:
EXEC sp_addlinkedserver
@.server = 'SEATTLE Mktg',
@.provider = 'Microsoft.Jet.OLEDB.4.0',
@.srvproduct = 'OLE DB Provider for Jet',
@.datasrc = 'C:\MSOffice\Access\Samples\Northwind.mdb'
GO
-- OR to use no named parameters:
USE master
GO
EXEC sp_addlinkedserver
'SEATTLE Mktg',
'OLE DB Provider for Jet',
'Microsoft.Jet.OLEDB.4.0',
'C:\MSOffice\Access\Samples\Northwind.mdb'
GO
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Neeraj" <Neeraj@.discussions.microsoft.com> schrieb im Newsbeitrag
news:FE0A89AE-C691-4D46-A2F5-CA38A7D161F8@.microsoft.com...
> Hello All,
> I am new to SQL Server. I have an Access database and I want o run SQL
> queries in SQL Server. I have alredy run SQL queries using this database
> in
> Access but cant do it in SQL Server. Can someone advise?
> --
> Neeraj

How to run queries in SQL Server using Access files

Hello All,
I am new to SQL Server. I have an Access database and I want o run SQL
queries in SQL Server. I have alredy run SQL queries using this database in
Access but cant do it in SQL Server. Can someone advise?
--
NeerajYes of course. You have to setup the access database as a linked server or
have to use the OPENROWSET or OPENDATASOURCE. I like working with linked
server if you use they permanently. You can access them via the four-part
name schema naming the object as below:
Select * from LinkedServername...ObjectName
More on this is on the BOL with some good examples, makes sure that the path
you define in the settings of the Access database is serverbased. so the
access db has to be reached from the server to use it.
Sample:
USE master
GO
-- To use named parameters:
EXEC sp_addlinkedserver
@.server = 'SEATTLE Mktg',
@.provider = 'Microsoft.Jet.OLEDB.4.0',
@.srvproduct = 'OLE DB Provider for Jet',
@.datasrc = 'C:\MSOffice\Access\Samples\Northwind.mdb'
GO
-- OR to use no named parameters:
USE master
GO
EXEC sp_addlinkedserver
'SEATTLE Mktg',
'OLE DB Provider for Jet',
'Microsoft.Jet.OLEDB.4.0',
'C:\MSOffice\Access\Samples\Northwind.mdb'
GO
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Neeraj" <Neeraj@.discussions.microsoft.com> schrieb im Newsbeitrag
news:FE0A89AE-C691-4D46-A2F5-CA38A7D161F8@.microsoft.com...
> Hello All,
> I am new to SQL Server. I have an Access database and I want o run SQL
> queries in SQL Server. I have alredy run SQL queries using this database
> in
> Access but cant do it in SQL Server. Can someone advise?
> --
> Neeraj

How to run an Access file from a SQL job?

I have an Access file that I'm using to move some data around. I want to run it from a SQL job. How do I do that? The Access file runs a command using AutoExec, so it doesn't have any interaction or anything. It just runs and closes itself. Thanks.

pse don't duplicate.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1947186&SiteID=1

Madhu

How to run Access file from SQL job?

I have an Access file that I'm using to move some data around. I want to run it from a SQL job. How do I do that? The Access file runs a command using AutoExec, so it doesn't have any interaction or anything. It just runs and closes itself. Thanks.

use XP_cmdshell

Executes a given command string as an operating-system command shell and returns any output as rows of text. Grants nonadministrative users permissions to execute xp_cmdshell.

check in BOL. IF its sql server 2005 you have to enable it using SQL Server 2005 features.

Madhu

How to rollback a transaction in Data Access Layer

Hi,

I am having a application in which from the front end i am saving details of three different things

i.Enquiry Details

ii.Parts Details

iii.Machine details

i am saving the Enquiry detail in a data table,Parts Details in a data table and machine detail in a data table and finally i am adding the three data tables into a single data set and passing the data set to data access layer there i have three insert command one for each data table in my case the enquiry data table will be saved first and then the next two details will be saved and i am saving the details in three different tables in the database, my problem is some times the enquiry details will save to the database and while saving the Parts details there may be some exception and i will throw an exception in that case the enquiry details will be saved and the remaining two details are not saved(Which are also part of the same Transaction).I wanted to know about how to call the transaction function in case of Data Access Layer.

Do you want to store the data independtly. Even the tail 2 tables failed you want to successfully commit the first insert?

Pls explain the last statement?

|||

You can use a user transaction which can be opend through Sqlconnection.begintransaction()

See the MSDN for more details:

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.begintransaction.aspx

The transaction is tied to the connection but can be commited separately.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks for Reply,

The answers for your questions are below

1.Do you want to store the data independtly?

yes i am storing the data in three different tables.

2.Even the tail 2 tables failed you want to successfully commit the first insert?

No i want to roll back the entire transaction.

How to revoke write, update access from public role

Hi,
I have an application that gives all the write, update access to public role
, how could I quickly revoke all the Write and Update access from Public, in
stead of uncheck all the checked boxes?
Thanks.Hi
Execute the following in Query Analyzer with text result (Query menu click
result in text) and copy and paste results
to give you the required script.
select 'revoke all on ' + QUOTENAME(name) + ' from [public]'
from sysobjects where type in('U','P','V','TR','IF','FN')
and objectproperty(id,'IsMSShipped')=0
go
Tahnks
Hari
MCDBA
"Hong" <anonymous@.discussions.microsoft.com> wrote in message
news:41AE885E-A0CB-4542-B6A9-843454B33A66@.microsoft.com...
> Hi,
> I have an application that gives all the write, update access to public
role, how could I quickly revoke all the Write and Update access from
Public, instead of uncheck all the checked boxes?
> Thanks.

How to retrieve Date fields from an Access MDF on VS c++ Net 2005

I Apologize if this isn't the forum to ask this...
I have a MS Access (MDB) file with a table with 2 date fields, i want to read from a dialog on my app (on MS Visual .NET Studio 2005), here's the code I've been using do far:

Code Snippet

hr=theApp.m_cs.Open(theApp.m_ds);
if(SUCCEEDED(hr)) {

theApp.m_cs.StartTransaction();

theApp.m_cs.Commit();
CCommand< CDynamicAccessor > cmd;
CComBSTR query(_T("SELECT NumContrato, NumClie, FechaC, FechaCob, Inversion, NoCobrador, NoVendedor, Total, Plazo, Pagos FROM Contrato"));
CString string(query.m_str);
cmd.Open(theApp.m_cs,string);

hr = cmd.MoveFirst();

query=static_cast< BSTR >(cmd.GetValue(1));
CString csres(query.m_str);
this->m_numc=(int)*(query.m_str);
query=static_cast< BSTR >(cmd.GetValue(2));
m_numcte=(int)*(query.m_str);
query=static_cast< BSTR >(cmd.GetValue(3));
//m_fecc=(int)*(query.m_str);

MessageBox(csres);
theApp.m_cs.Close();
}

FechaC, FechaCob, are the two Dates I want to retrieve, but when I debug, it reads a 0 (zero) from the date fields, is there a limitation? can they be read? is there a special way to read them?
> thanks in advance!

--
Me!

I'm not experienced in templates, but it looks strange for me and you should check the type of the returned value.

If your field is of the type Date/Time then I'm not sure that simple casting is correct, since I would expect GetValue to return the pointer into the buffer with the actual data and I presume that the datatype there should be DBTYPE_DATE. Perhaps you need to create a specific accessor and explicitly request conversion to a string type.

|||Thanks A lot this is what i've done:

Code Snippet

DATE *d=(DATE*)(cmd.GetValue(3));

COleDateTime D(*d);

m_fec=D;

Sorry for the such a noob question you've been helpful! :)!

How to restrict editing of SQL Server 2005 data via ODBC link?

I have a sql server 2005 database with Delphi 2006 in the front end and for querrying and reporting we use MS Access 2003 by connecting to this database via ODBC connection. I recently found out that the SQL Server 2005 data connected thus can be edited (updated) from MS Access. I do not want end users to modify/update the SQL Server 2005 data from MS Access while I also want them to have the ability to insert/update/delete rights using the appropriate application interface. For now, I am handling this by creating a user id that is not permitted to update, insert and delete and using the same account in the ODBC. Is there a way in SQL Server 2005 you can control insert/update/delete rights for all users that will be applicable only in the ODBC mode?

Any help will be greatly appreciated.

thulo

Hi Thulo,

If I understand your question correctly, you want to be able to grant permissions to database objects based on the type of the client (ODBC, OLEDB, SqlClient, etc.), is this correct? Unfortunately, this isn't possible by design - the main goal is to provide the same functionality no matter what client is used. Instead, the SQL Server security model recommends what you are actually already doing - grant permissions "per database user". The user is the main permissions-related abstract here (having in mind the schemas, too). The user is related to the corresponding login object, which controls the connectivity part (having in mind endpoints permissions, too).

I understand that the user-based permissions concept might seem like more work, but it would pay off long term when the requirements to your application change.

HTH,
Jivko Dobrev - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi Jivko,

You got my question right and that answers my question. Thanks so much for your help.

thulo

How to restrict access to database to only IUSR_<machinename>

Hello Mark,
Thanks for your message.
What do you mean " I removed TCP/IP and Named Pipes from the SQL Server
Registration from within Enterprise Manager and rebooted the machine."? Do
you mean you have performed the following steps?
1. Right-click 'MYSERVER\SERVER' in SQL Enterprise Manager(SEM), click
Properties.
2. Click General tab in the Properties window, click Network Configuration.
3. Remove TCP/IP and Named Pipes from the "SQL Server Network Utility."
Please let me know what steps you performed.
If SQL Server service didn't start, SQL Server Agent service will not be
started. Please make sure the SQL Server service has been started. You can
follow the steps below to start SQL Server service:
Click Start > All Programs > SQL Server > Service Manager > start SQL
Server service in Service Manager > start SQL Server Agent service in
Service Manager
If you still are unable to start the SQL Server Agent service, please
follow the steps below to attempt to start the SQL Server Agent Services
from a command line, then send the error log files to me.
1. Open a command line window.
2. Run the following command in the command line window:
"C:\Program Files\Microsoft SQL Server\<instance name>\Binn\sqlagent" -c -v
Note: Above command is just a sample. You need to replace the directory
"C:\Program Files\Microsoft SQL Server\<instance name>\ with the real
directory which you installed the SQL Server.
3. Compress all the log files under the directory "C:\Program
Files\Microsoft SQL Server\<instance name>\LOG" and send it to me for
research.
In addition, you can open "SQL Server Network Utility." by clicking Start >
All Programs > SQL server > SQL Server Network Utility, then you can
re-enable TCP/IP and Named Pipes in the SQL Server Network Utility.
If anything is unclear, get in touch.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.>>Do you mean you have performed the following steps?
Yes, that is correct. When I did that, and rebooted the machine, the
SQLServerAgent would not start. (It would start, then immediately stop).
Therefore I could not open my database using Enterprise Manager.
NOTE: I had also set the 'Hide Server' switch, and suspect this may have
been part of the problem as well.
SOLUTION: I went into the registry under
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Mi
crosoft SQL
Server\MIRACLECAT\MSSQLServer\SuperSocke
tNetLib\Tcp and set the TcpHideFlag
back to zero and then rebooted and all worked again.
I think I may just depend on Windows Server 2003 blocking post 1433 (SQL
Server Port) and let it go at that since temporarily losing my SQL databases
put quite a scare into me
Thanks for all your help however.
Mark
"Sophie Guo [MSFT]" <v-sguo@.online.microsoft.com> wrote in message
news:HCsqCPf%23EHA.3360@.cpmsftngxa10.phx.gbl...
> Hello Mark,
> Thanks for your message.
> What do you mean " I removed TCP/IP and Named Pipes from the SQL Server
> Registration from within Enterprise Manager and rebooted the machine."?
> Do
> you mean you have performed the following steps?
> 1. Right-click 'MYSERVER\SERVER' in SQL Enterprise Manager(SEM), click
> Properties.
> 2. Click General tab in the Properties window, click Network
> Configuration.
> 3. Remove TCP/IP and Named Pipes from the "SQL Server Network Utility."
> Please let me know what steps you performed.
> If SQL Server service didn't start, SQL Server Agent service will not be
> started. Please make sure the SQL Server service has been started. You can
> follow the steps below to start SQL Server service:
> Click Start > All Programs > SQL Server > Service Manager > start SQL
> Server service in Service Manager > start SQL Server Agent service in
> Service Manager
> If you still are unable to start the SQL Server Agent service, please
> follow the steps below to attempt to start the SQL Server Agent Services
> from a command line, then send the error log files to me.
> 1. Open a command line window.
> 2. Run the following command in the command line window:
> "C:\Program Files\Microsoft SQL Server\<instance
> name>\Binn\sqlagent" -c -v
> Note: Above command is just a sample. You need to replace the directory
> "C:\Program Files\Microsoft SQL Server\<instance name>\ with the real
> directory which you installed the SQL Server.
> 3. Compress all the log files under the directory "C:\Program
> Files\Microsoft SQL Server\<instance name>\LOG" and send it to me for
> research.
>
> In addition, you can open "SQL Server Network Utility." by clicking Start
> All Programs > SQL server > SQL Server Network Utility, then you can
> re-enable TCP/IP and Named Pipes in the SQL Server Network Utility.
> If anything is unclear, get in touch.
> Sophie Guo
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> ========================================
=============
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>

How to restrict access to database to only IUSR_<machinename>

Hello Mark,
Based on the problem description, it seems that you'd like to let all the
application which is running internally can access SQL server successfully.
However, outside users cannot access SQL server from internet.
If this is what you want, you can remove TCP/IP and Named Pipes in the "SQL
Server Network Utility." You can follow the steps below:
a. On the Microsoft SQL Server 2000 server, start the SQL Server Network
Utility.
b. Click the General tab, and then select the instance you want from the
Instances drop-down menu.
c. Highlight TCP/IP, and then click Disable.
d. Highlight Named Pipes, and then click Disable.
By doing so, users still can access SQL server internally instead of
internet.
I hope above information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.I think I boo-boo'd
I removed TCP/IP and Named Pipes from the SQL Server Registration from
within Enterprise Manager and rebooted the machine.
Now the SQL Server agent won't start, and therefore I can't get into the SQL
Server Registration again since the agent isn't running.
The SQLServerAgent is stopped, and when I attempt to start it, it just
starts and immediately stops again.
The event viewer shows the following:
SQLServerAgent could not be started (reason: Unable to connect to server
'MYSERVER\SERVER'; SQLServerAgent cannot start).
Is there anything I can do to recover?
Thanks!
"Sophie Guo [MSFT]" <v-sguo@.online.microsoft.com> wrote in message
news:OWhx%23YW%23EHA.3360@.cpmsftngxa10.phx.gbl...
> Hello Mark,
> Based on the problem description, it seems that you'd like to let all the
> application which is running internally can access SQL server
> successfully.
> However, outside users cannot access SQL server from internet.
> If this is what you want, you can remove TCP/IP and Named Pipes in the
> "SQL
> Server Network Utility." You can follow the steps below:
> a. On the Microsoft SQL Server 2000 server, start the SQL Server Network
> Utility.
> b. Click the General tab, and then select the instance you want from the
> Instances drop-down menu.
> c. Highlight TCP/IP, and then click Disable.
> d. Highlight Named Pipes, and then click Disable.
>
> By doing so, users still can access SQL server internally instead of
> internet.
> I hope above information is helpful.
> Sophie Guo
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> ========================================
=============
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>

How to restrict access to database to only IUSR_<machinename>

I am running IIS and SQL Server on the same Win2003 Server machine. Is there
a way to set up security such that only IUSR_<machine> is allowed to access
the SQL Server 2000, even if outsiders have the connection string to the
database?
Thanks for any assistance!only create the account iusr_computername
"Mark Findlay" <mfindlay@.speakeasy.org> wrote in message
news:%23OLNjKN%23EHA.2192@.TK2MSFTNGP14.phx.gbl...
> I am running IIS and SQL Server on the same Win2003 Server machine. Is
there
> a way to set up security such that only IUSR_<machine> is allowed to
access
> the SQL Server 2000, even if outsiders have the connection string to the
> database?
> Thanks for any assistance!
>|||Unfortunately that's not a reasonable alternative for me since there are
many accounts on that machine that make use of other resources on that
machine.
I believe the solution is to block port 1433, but I don't know how to block
port 1433 on Windows Server 2003. Can anyone assist?
Thanks!
"Olu Adedeji" <i-oluade@.microsoft.com> wrote in message
news:eKLc6hQ%23EHA.2452@.TK2MSFTNGP14.phx.gbl...
> only create the account iusr_computername
> "Mark Findlay" <mfindlay@.speakeasy.org> wrote in message
> news:%23OLNjKN%23EHA.2192@.TK2MSFTNGP14.phx.gbl...
> there
> access
>

How to restrict access our DB on SQL server.

Hi,
How can we restrict our DB if connecting thru other applications like SQL query analyzer, Excel & even Enterprise manager?
but this DB should be accessible by our application (from front end) only...
I learned about "Application Role" in SQL server 2000 but to apply this
we will have to change our code thousand places.
Have any idea...Pl. do suggest?
Thanx in advance!!!!
Regards,
Paresh...
Message posted via http://www.sqlmonster.com
Don't give out the passwords and they won't be able to login. If they have
a login and password then they can get in and do what ever the current
permissions allow them to.
Andrew J. Kelly SQL MVP
"paresh goyal via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:d15479cc26b24cf0a782a131acead60a@.SQLMonster.c om...
> Hi,
> How can we restrict our DB if connecting thru other applications like SQL
> query analyzer, Excel & even Enterprise manager?
> but this DB should be accessible by our application (from front end)
> only...
> I learned about "Application Role" in SQL server 2000 but to apply this
> we will have to change our code thousand places.
> Have any idea...Pl. do suggest?
> Thanx in advance!!!!
> Regards,
> Paresh...
> --
> Message posted via http://www.sqlmonster.com
|||Sounds like you're looking for a solution that might not exist. If your
users that currently use the applications login to SQL with their own
userids, they can use the same userids with any query tool and do whatever
they have permissions for. You could try to use different ports, server
aliases, etc... to make it tougher for them to discover where the actual
sql db sits but it's just a speed bump, not a barrier.
aK.
"paresh goyal via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:d15479cc26b24cf0a782a131acead60a@.SQLMonster.c om...
> Hi,
> How can we restrict our DB if connecting thru other applications like SQL
query analyzer, Excel & even Enterprise manager?
> but this DB should be accessible by our application (from front end)
only...
> I learned about "Application Role" in SQL server 2000 but to apply this
> we will have to change our code thousand places.
> Have any idea...Pl. do suggest?
> Thanx in advance!!!!
> Regards,
> Paresh...
> --
> Message posted via http://www.sqlmonster.com