I would like to eliminate all hardcoded login ids and passwords and would like to know what other companies are doing to address this issue. I know a job scheduling software package is the ideal solution, but until dollars become available I need a short-term solution. We've tossed around the idea of using system variables.
Any recommendations?
Thanks, Dave
Hi all,
I have setup a new SQL 2000 SP4 and internal auditor query about revoke permission from Public role and remove guest from all databases.
1. Can I revoke all default permissions (select on system tables in all DBs) from "Public" role? I am concern any error after such action.
2. I found that guest account in DB -- master, tempdb and msdb. According to Microsoft documents. The account should not remove and can't from master and tempdb. How about msdb?
Thanks,
Regards,
Edwin
Hi all ,
There is a table named "Users" in my SQL Server DB , there is password field that I'm going to make it secure , Now I explain you How I plan to this :
There are two columns named "SID1" & "SID2" other than "Password" Field in this table and their datatype are all VarBinary ,
When I want to add user to this table , two random byte arrays are generated using RNGCryptoServiceProvider Class , then I combine SID1,SID2 and Password byte arrays to generate password , after that this byte array is Hashed using
SHA384 Class and is stored into database , Also two randomly generated byte arrays are stored in SID1 & SID2 Fields , For Comparison , I do all above steps reversely , Up to here there is no problem and everything work OK , but I have two questions ,
1) Is this way of storing password secure?
2)Isn't it better to put these codes into a CLR Integrated Class and do all above steps in SQL-Server-Side?If Yes , I should send my clear-text password to sql server For Hashing and Validating and this password could be stolen using a packet sniffer , What Should I do in this Step? Installing SSL?but I'm in a workgroup environment , How can I Install SSL in workgroup environment? Isn't there any other solution?
Thanks in advance for your great helps ,
Cheers
Is it possible for a batch command to give access or add a new role for the user to database? Basically:
USE my_Database
CREATE USER [User1] FOR LOGIN [User1] WITH DEFAULT_SCHEMA=
[dbo];
EXEC sp_addrolemember 'Application1', 'User1'
EXEC sp_addrolemember 'SQL_Read_Only', 'User1'
If User1 was already created then ignore that and run the exec sp_addrolemember. Next,Â
EXEC sys.sp_change_users_login
@Action = 'Update_One',
@UserNamePattern = 'User1',
@LoginName = 'User1',
@Password = NULL
to link the orphan user within database.
i am testing some encryption
scenarios ,in profiler the statements like "OPEN KEY" and all "Encrypt"
and "Decrypt" functions are removed automaticly from the trace
and replaced with a comment ,create a trace and try the code i attached ,
you will see in profiler trace that that all encryption related commands
are commented out ,this is what expected.
but now go to the batch and comment out the "SELECT @rrr' statment,
and run the batch ,this batch will fail beacuse "@rrr" is not declared,
now go back to profiler and you will see that for the failed batch
all the encryption command are NOT COMMENTED OUT !!!
esspecially important is the visibility of the password of the open key command.
seems like a very dangerous bug to me!!!
CREATE CERTIFICATE test1
ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'
WITH SUBJECT = 'Sammamish Shipping Records',
EXPIRY_DATE = '10/31/2009';
GO
CREATE SYMMETRIC KEY Key09 WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE test1;
GO
declare @Str nvarchar(100)
declare @Enc varbinary(max)
set @Str = 'encrypt this'
OPEN SYMMETRIC KEY Key09
decryption by CERTIFICATE test1 WITH PASSWORD = 'pGFD4bb925DGvbd2439587y'
SET @Enc
= EncryptByKey(Key_GUID('Key09'), @Str);
---select @rrr
select CONVERT(nvarchar(100), DecryptByKey(@Enc))
go
Users were able tolog on to our SQL 2000 servers with their passwords being case-insensitive.
Now with SQL 2005 some users can't logon because SQL 2005 passwords are case-sensitive.
Can SQL 2005 be configured so that passwords are NOT case sensitive???
Thanks!
I need to create a script which iterates over a list of about 40,000 id's. As the list goes through each id other stored procedures are called which do something. Because of access rights on the server im running the script i need to hard code the 40,000 records in my script. The id's are currently in an excel file
What way can i store them in the script, should i use a cursor, and if so how can i create a cursor to hold so many hard coded values
Any help or alternative ideas would be much appreciated
This is a reoccurring error in one of our Domain Controllers event logs…
Event Type: Error
Event Source: KDC
Event Category: None
Event ID: 11
Date: 7/24/2007
Time: 6:23:05 AM
User: N/A
Computer: DC2
Description:
There are multiple accounts with name MSSQLSvc/dbsrvr-02.olntv.local:1433 of type DS_SERVICE_PRINCIPAL_NAME.
the Server dbsrvr-02 (sql server 2000 standard edition sp3a) has a default instance and it looksl ike 2 named instances installed that we are not using. Ive searched this error and cannot find a thing.. should this concern me??? anyone ???
I need to upgrade the currect db to sqlserver2005, and the current db used multiple filegroup and file
I first detach the db, and I want to make sure if I need to attach each db file after upgrade to sqlserver2005
Thanks
I am getting a bit concerned with the size of my log file and my understanding of backups and how the log file should be getting reduced in size. I have a production database that is 12 GB and the log file is 275 GB. The database file is set to autogrow at 1 MB and unrestricted file growth. The log file is set to 10% file growth and restricted to 2,097,152 MB file growth. I perform a full database backup each night. I had thought that all transactions in the log file would be rolled into the database file and then the log file auto-truncated in size during the backup process. I have never seen a log file stay larger than the database file. Please advise how I may keep the log file size (growth) down. Thanks!
Hi all,
We are using SQL Server 2005. The auto update statistics and auto create statistics for a database is set to ON. This database has a very heavy work load. When I checked the individual statitics , still the last updated statistics is in a old date value (few months ago).
Is it necessary to manually update the statistics for the same database? Or can we rely upon "auto update statistics" itself ?
Usually in what frequency the manual UPDATE STATISTICS should be run on production system which has heavy transactions ?
Thanks & Regards,
Hariarul
Hello,Our company has developed several Access applications for our clientsand we wish to expand to use a database server. We wish to use Accessas the front end application and SQL Server 2005 as the back endDatabes Server. Making a Server-Client application database.I'm very new to SQL Server so my concern is how the licensing worksfor a developer like us. I am aware that we can purchase the Developeredition for $49 USD. Our clients will then have to purchase a theirrespective licensing option.We do not have an issue with this, but i would like a betterunderstanding of how this works.1) The developer edition (DE) is the same as the Enterprise edition.Would our clients have to buy the Enterprise edition ($24,999) if wedevelop the SQL server in the DE? Or can they purchase the Standardor Workgroup edition if the workload allows it?2) If the client already has the Enterprise Edition on the Server wewish to use, will they have to buy the license again if we create theDatabase? Can they import our Database to thier SQL Server, and ourapplication connects to thiers?3) Is there other options that would require a cheaper option for ourclients?4) With just the DE, can we develope applications and use theappropriate edition for our clients whatever it is?5) To test our products we thought of using the Trial version tosimulate a multi-user test environment? Since thats just a 180 daytrial, perhaps the Worgroup Edition or the Light weight Edition mightwork. But these editions might not be compatible with our clients'requirements. The DE doesn't seem to handle multi-user environments,so we cant effectively test how the application will perform in the"real world". Any advices here?Our clients range from small bussiness to nation-wide to multi-national companies.Thanks,- GL
View 14 Replies View RelatedI just started looking into writing queries/SPs in C#. A simple SP looks like this.
public class ContactCode
{
[SqlProcedure]
public static void GetContactNames(string lastName)
{
SqlCommand cmd = €¦€¦. €¦€¦
cmd.CommandText = "SELECT * FROM Person.Contact WHERE LastName = " + lastName;
SqlDataReader rdr = cmd.ExecuteReader();
SqlPipe sp = €¦€¦€¦€¦..;
sp.Send(rdr);
}
}
Since it's concatenating a dynamic SQL in the code, is SQL injection a concern or the CLR integration knows to take care of the input sanity? I know good programming practice is to validate input before it gets to this but it's necessary for CLR SPs to have the same robustness as normal T-SQL SPs when it comes to input parameter handling.
HELP,
I need to take a variable from a tabel in SQL Server pass to a Batch file and execute the batch file. Right now I can exec the batch file with XP_CMDSHELL but how can I pass the variable to the batch file and loop through all the variables.
Please help
Phil
I am using the following batch file to execute a script that creates a db and all its objects in the local sql express:
sqlcmd -S (local)SQLExpress -i C:CreateDB.sql
This works fine, but I'm wondering if there's an easy way to put the script in the batch file, so users don't have to worry about putting the script in the C drive. I tried getting rid of the i parameter and pasting the script from the sql file into the batch file, but it didn't work.
Thanks,
Dave
Is there a way I can enable my end users to change
their own passwords within SQL 6.5 every 30 days to
something unique?
Currently, I don't see that they have the option to change
it anywhere.
Any thoughts?
Thanks!
Toni
I have to create some type of an sp that will force users to change
their passwords every 30 days. It sounds like I should be able to
create a simple table with the login and the password expiration date.
Then, I should create some type of function to check the expiration, get
the new password and run sp_password. Has anyone done this before?
Where would this table be created? In Master? Would that also be where
I should create this sp? Should this be an xp?
I'm fairly light on creating sp's. Can anyone suggest a starting point
for me or give any suggestions?
I would appreciate any help.
Thanks!
Toni E.
Does some one know of a way to use windows passwords in sql w/o having to enter them in? Like a program or sql statement?
As of now, we are having to enter them in ourselves , and would like to make our jobs easier....Thanks!!
hey guyz
in the database, is there anyway to chnage the password field to * instead of showing what is inside?
Hi
I have SQL Server 2000 and in one of the databases we store the Application passwords as a Clear text. we would like to encrypt
these passwords so that we will pass the auditing.
Can some one please suggest a good way to encrypt these passwords.
Thanks
for example i will set the password as chicken
http://img515.imageshack.us/my.php?image=chickenqs6.gif
then i press ok and it seems like it works i open it right away and
http://img295.imageshack.us/my.php?image=notchickenao0.gif
^^clearly not chicken......
then,ontop of that when i open the login name is a query script its some 15 character password that everytime u open in script the code changes..
http://img401.imageshack.us/my.php?image=screenhunter04jan062317zu0.gif
any help would be greatly appreciated
I have a pgp file requiring a password that is emailed to me. Is it possible to set up a DTS package that will open the file (using the password), and insert the data into a table, file, etc.
TB
Hello,
Is there a way to pull user id's and passwords from a database?
TIA,
Anita
I have a problem where our developers would like to change the passwords of the SQL Logins without knowing the users old password.
The only way they can use the below syntax is that they must be a member of the SYSAdmin Server Role.
sp_password NULL, 'newpassword',Max
I need a way to allow them to change the passwords without making a user a member of the SYSAdmin Server Role.
Any thoughts would be greatly apprecitated. Thanks in advance.
Max
Hello all,
Is there anyway to tell in sqlserver when a users' password has been changed?
Is there a way that I can prevent SQL Authenticated users from changing their passwords?
View 6 Replies View RelatedHi All,
Sql server 7
There are set of logins in sql server some are NT authentication and some are sql server authentication. How can i retrieve the passwords for both these logins.
TIA
Adil
How can we change the passwords of users in 2005 and in 2000
View 2 Replies View Relatedi have been reading many asp.net books
where is a good place to hold user name and password while the user is on your side in a cookie or applicaion user variable?
and if the user closes the navigator..what command removes the log in information ?
A friend of my self asked me how he can save a password not as clear text. He wanted to encrypt the password and save the encrypted string in the database.
How can he do this? Maybe somebody can help me here.
Regards Markus
I have installed Sql Server 2000 Reporting Services.
I desinged report in VS 2003.When press F5 key.Every time it is asking for user id password then only it is giving List of Report Names.
Is there any way to avoid giving user id and pwd every time.
Regards.
I use from sql server 2008. and c#
what is the best connectionstring?
I don't know if i use Persist Security Info and Integrated Security or not?
And if yes then their value must be true or false?
I'm looking for a way to upgrade the passwords of all users in a database every 10 days.
What I have is, generating random passwords, md5 encryption etc... but still clueless about how I should update em every 10 days.
Thanks in advance