Acessing SSAS

Hi,

i am developing a BI project for a client with SQL Server 2005. I am able to access the Database Engine on my client's server through Management Studio using a specific account but i can't acess Analyis Services or Integration Services because i can only connect through Windows Authentication (all options on the connect window are greyed out) and my user is not a domain user.

How can i solve this?

Best Regards,

Tiago

Are you certain that SSAS and SSIS are online and that your login has permissions.

Acessing SQL through a DOS login script

Does anybody know if it is possible to pass an NT Environment variable to an SQL Server and then return the results back to the DOS?

Example: when a user logs in, their username is stored in the environment variable %USERNAME%. I would like to send this to SQL and then look up their department and then depending on what department they are in, different drives are mapped.

I think this would be done with stored procedures, but I am quite new to these.

Many thanks in advance
Matt CottrellYes I believe you can, try this from DOS, substring in your login criteria

isql -Uxx -Pxx -Sxx -n -Q"PRINT '%USERNAME%'"

You can then create a stored procedure that "PRINTS" out the drive mapping

"NET USE D: \\Server\Home "

Have your script file pass the USERNAME in then redirect the output to a file and run the file from the DOS command script

Example
isql -Uxx -Pxx -Sxx -n -Q"sp_MakeMappings '%USERNAME%'" -oC:\temp\mapping.cmd

C:\temp\mapping.cmd

del C:\temp\mapping.cmd

Acessing SQL Server over the web with shared IP

I am new to setting up SQL Server and would greatly appreciate if
someone can help me out with this issue:

I have successfully set up SQL Server but want to be able for clients
of mine to be able to connect to it via the web through enterprise
manager. I have tried everything I could think of to get it to work. I
made sure the correct ports where open in the firewall (1433,1434).
Everytime I try to connecting using the IP of the box that the SQL
Server resides I get SQL Server does not exist or access denied. I
verified that the login credentials being used for the database I am
trying to correct to are correct. Still the same error message.

How I would like to set it up would be to create a subdomain on the
box and point it to SQL Server so that is what would be entered when
registering the group in enterprise manager.

I am currently running all my sites through vhost on a single IP using
IIS 2003. SQL Server is installed on the box where all of the sites
are being served up.

Is it impossible to set-up SQL Server to allow connections over the
web when using a Shared IP?

I have been researching this for over a week now and hope someone out
there can help me.

Thanks in advance!!Chris (melovepoi@.yahoo.com) writes:
> I am new to setting up SQL Server and would greatly appreciate if
> someone can help me out with this issue:
> I have successfully set up SQL Server but want to be able for clients
> of mine to be able to connect to it via the web through enterprise
> manager. I have tried everything I could think of to get it to work. I
> made sure the correct ports where open in the firewall (1433,1434).
> Everytime I try to connecting using the IP of the box that the SQL
> Server resides I get SQL Server does not exist or access denied. I
> verified that the login credentials being used for the database I am
> trying to correct to are correct. Still the same error message.
> How I would like to set it up would be to create a subdomain on the
> box and point it to SQL Server so that is what would be entered when
> registering the group in enterprise manager.
> I am currently running all my sites through vhost on a single IP using
> IIS 2003. SQL Server is installed on the box where all of the sites
> are being served up.
> Is it impossible to set-up SQL Server to allow connections over the
> web when using a Shared IP?

I'm not really sure what you mean with "connect to it via the web through
enterprise manager". Do you mean that they will connect over the Internet?
If they were to go from EM through a web site to SQL Server sounds a
little funny to me.

As for why things are not working, I don't really have much advice, but
I take the occassion to respond, because you may not want to do that
after all.

Exposing SQL Server directly on the Internet is something you should
avoid. And if you do it - make sure that you have SQL Server authentication
turned off. There is no protection in SQL Server against brute-force
attacks, other than logging, so no matter how strong password you have
for "sa", it is likely to be cracked sooner or later if someone is dead
set on doing it. I don't know if you are able to authenticate your clients
through Windows authentication. If you are, you might be safe. But it
might be a better option to offer them a VPN connection from which they
can access SQL Server that way.

What I can add about the error message "SQL Server does not exist or
access denied" is that playing with login credentials on SQL Server
is of no use. The error means that you never find the server, so it
is not able to authenticate you anyway.

This KB article discusses this message more in detail:
http://support.microsoft.com/defaul...B;EN-US;q328306.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||In article <1f86f93.0408201949.5a756cf3@.posting.google.com>,
melovepoi@.yahoo.com says...
> I am new to setting up SQL Server and would greatly appreciate if
> someone can help me out with this issue:
> I have successfully set up SQL Server but want to be able for clients
> of mine to be able to connect to it via the web through enterprise
> manager. I have tried everything I could think of to get it to work. I
> made sure the correct ports where open in the firewall (1433,1434).
> Everytime I try to connecting using the IP of the box that the SQL
> Server resides I get SQL Server does not exist or access denied. I
> verified that the login credentials being used for the database I am
> trying to correct to are correct. Still the same error message.

First, let me say one important thing - exposing your SQL server
directly to the internet is always a BAD IDEA. There is no reason to
expose it directly to the internet. You should have the users VPN into
your server and access the SQL Server through the VPN.

It was servers like yours that let slammer spread around the world in
record time.

> How I would like to set it up would be to create a subdomain on the
> box and point it to SQL Server so that is what would be entered when
> registering the group in enterprise manager.
> I am currently running all my sites through vhost on a single IP using
> IIS 2003. SQL Server is installed on the box where all of the sites
> are being served up.
> Is it impossible to set-up SQL Server to allow connections over the
> web when using a Shared IP?

Since you have a router with port-forwarding (or a firewall with port
forwarding) you need to tell people to use your PUBLIC ip address when
they are outside of your network - external people access the SQL Server
using the Public IP that you forwarded to the internal IP.

One other thing - don't give users access to the SA account and don't
give them access to any other database (just their own). We did some
work for a company that let users access the DB over the net, the
company had created super user accounts for people to manage their
database remotely - as it turns out, the accounts had access to every
database in their system - some 30 databases on the server. It was
interesting to run an audit, find personal financial info on the other
databases, and then contact the ISP and the Owners of the databases to
let them know that anyone with access to any database could see all of
them as SA. The entire thing was taken off-line for a week while they
figured out how to manage it (should have taken about an hour).

I strongly suggest, even BEG, that you have users VPN into the
network/server and then gain access to the SQL server - do not expose it
directly to the internet.

--
--
spamfree999@.rrohio.com
(Remove 999 to reply to me)|||Hi Chris

It is possible to allow access via the web. You need to use Enterprise
Manager from 2000 upwards and to check that TCP/IP is the connection
protocol. I would suggest not using the standard port but moving it
much higher up the range to a more 'random' port (purely to reduce
risk of hacking).

I'm not exactly sure what you mean by creating a sub-domain on the
box. My interpretation is that you want to create something like
subdomain.mydomain.com and use that to connect to the database. This
you can do, but not on the box. You will need to talk to your ISP and
get them to create the subdomain and point it to the IP address of
your server. Then I think you will be able to register the server by
entering subdomain.mydomain.com in EM.

Hope this helps

Sam

Acessing deleted records

Hi

First i'd like to say that i dont really know if it is possible to acess this, but since i've been told that some database system sometimes only "flag" record as deleted to delete them later and that i would need to possibly acess this data for a work im doing, it would be interesting for me if somebody had the asnwer i seek.

Do you know if there is a way under sql server 2000/2005 acess those data?

In SQL Server 2005 you could use OUTPUT clause (http://msdn2.microsoft.com/en-us/library/ms177564.aspx):

Code Snippet

DELETE Sales.ShoppingCartItem
OUTPUT DELETED.* ;

In SQL Server 2000 you could create on delete trigger and use "virtual table" deleted

|||

Ok, i was not very clear in the first post, my bad.

Is it possible to acess deleted data, after it was deleted, and by that i mean : Someguy delete a record, and at the end of the week i want to know how much or what data has been deleted if it has not yet been deleted physically.

And i would prefer not to use trigger since i would have to make one for each and every hundreds table of the same number of databases.

|||

No, data is deleted physically in SQL Server as soon as the statement is committed. You *can* look in the logs to see what has been deleted using a third party tool like http://www.apexsql.com/sql_tools_log.asp, but I would only suggest that as a detective tool, not a common surveilance method, especially since you should be dumping your log fairly often as a method of recovery. It will also not record who delete the row, only that it has been deleted. If you want deletes to be logical and not physical, you would have to prepare this in your design and implement it yourself.

A trigger is really your best option for logging changes. Creating a trigger for all tables is a pain, but you might be able to build them without much trouble by using the information_schema and system views (or system tables in 2000). You can find out the tablename, the columns, etc and build the triggers using a standard template without a tremendous amount of work.

For just what data is different, you could try something like http://www.red-gate.com/products/SQL_Data_Compare/index.htm. It might be too slow for you if you have a lot of data. It also cannot tell you what has been deleted.

|||

For those who would like to know i found the solution to get what i wanted :

The command sp_spaceused give a lot of information about a table or a database including the unused space and row count.

Knowing that i was able to get the number of deleted record by comparing the size taken by undeleted row.